Checking for Multiple Conditions, Randomly Assigning Variable if True

[Young_workinprogress]

Hello All, I have what I think may be a "simple" IF statement function, but I'm having trouble ensuring the "variable" can be randomaly assigned" to a category (depending on the results of the IF statement). So if you take a look below I'll try to explain. I have a set of survey respondents. The respondents could me a member of 1, 2, or 3 organizations. For analysis purposes, I need to treat each respondent as if they only belong to one organization; So if a person happens to belong to 2 organizations, I want to "randomly" assign them to only one group in which they belong and analyze their data accordingly. For Example,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Respondent ID[/TD]
[TD]Org 1[/TD]
[TD]Org 2[/TD]
[TD]Org 3[/TD]
[TD]Additional Info[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]No [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]cda[/TD]
[/TR]
[TR]
[TD]432[/TD]
[TD]Yes [/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]532[/TD]
[TD]Yes[/TD]
[TD]No [/TD]
[TD]No[/TD]
[TD]zyx[/TD]
[/TR]
</tbody>[/TABLE]

I want to ensure that the respondent is "randomly" assigned to one of the organizations, but the respondent must actually belong to that organization before being assigned to it.
Logic: Look at respondent 1, check what organizations they belong to, randomly assign the respondent to only 1 organization. **A bonus would be to transfer all the respondent's additional info (in that row) to the new sheet/row/column. ** NOT a necessity though

 

[sdruley]

06.27.2013-17.24.02 - sdruley's library

Random assignment is made in the yellow section. The 3 shown, for example, in the Org1 column of membership means that respondent #3 was shown to be a member of Org 1 three times.
I am not sure that I understood all of your guidelines but at least this is a first shot.

 

[Young_workinprogress]

Sdruley,
Thank you for your reply, but I think we're not quite on the same page. I apologize for not being clear.
I'll explain it again using your example above.
Assume "1" mean YES (I am a member of Org) and "2" means (I am not a member of Org"); so respondent #1 belongs to Org 1 and Org 2, but does not belong to Org 3. I want to randomly assign respondent #1 to EITHER Org 1 or Org 2. So the output in the RANDOM column should be "Org 1" or "Org 2."

With my dataset, it is possible for respondents to be a member of ALL Orgs, 2 Orgs, or 1 Org.
The goal is to randomly assign each respondent to only ONE Org.

 

[markmzz]

Maybe this array formula (use Ctrl+Shift+Enter and not only Enter):

=IFERROR(CHOOSE(SMALL(IF(B2:D2="Yes",COLUMN(B2:D2)-COLUMN(B2)+1),RANDBETWEEN(1,COUNTIF(B2:D2,"Yes"))),"Org 1","Org 2","Org 3"),"")

Or

=IFERROR(INDEX(B$1:D$1,SMALL(IF(B2:D2="Yes",COLUMN(B2:D2)-COLUMN(B2)+1),RANDBETWEEN(1,COUNTIF(B2:D2,"Yes")))),"")

Markmzz

 

[Young_workinprogress]

So, the array assigns all members to the same organization.
regardless of if they are actually a member or not. I'm not quite sure of how your formula works, so I can't say more than that.
Thanks for the help.

---

[TABLE="width: 314"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]ID
[/TD]
[TD="width: 64, bgcolor: transparent"]org1
[/TD]
[TD="width: 64, bgcolor: transparent"]org2
[/TD]
[TD="width: 64, bgcolor: transparent"]org3
[/TD]
[TD="width: 162, bgcolor: transparent"]random ORG assignment
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"]yes
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]no
[/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Org 2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Here it work ok. Look at this:

A B C D E F
Respondent ID Org 1 Org 2 Org 3 Additional Info Rand Org
123 yes No yes abc Org 3
321 No Yes Yes cda Org 2
432 Yes Yes Yes xyz Org 1
532 Yes No No zyx Org 1

Could you post your formula?

Markmzz

 

[sdruley]

06.27.2013-20.25.06 - sdruley's library

 

[Young_workinprogress]

Thank you, Markmzz. I got it working after taking a minute to adjust the rows. <br> Any idea if its possible to set the "seed" so that I could replicate these results in the future? <br> If not, no worries, you've been a great help

 

[Young_workinprogress]

Thanks sdruley, I now have two solutions. I appreciate the help. <br> Any idea if its possible to set the "seed" so that I could replicate these results in the future? <br> I really appreciate the help!

 

[markmzz]

Sorry, but I didn't understand what you want.

Could you explain?

Markmzz