Find majority match in groups of cells

[nosila]

I'm analyzing Mechanical Turk results. Each assignment was done three times, and I want to find all sets of three assignments that were agreed upon by the majority (2/3 or 3/3) of respondents.

Here's the basic setup:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD]Org 1[/TD]
[TD]active[/TD]
[/TR]
[TR]
[TD]Org 1[/TD]
[TD]not.active[/TD]
[/TR]
[TR]
[TD]Org 1[/TD]
[TD]not.active[/TD]
[/TR]
[TR]
[TD]Org 2[/TD]
[TD]in.db[/TD]
[/TR]
[TR]
[TD]Org 2[/TD]
[TD]in.db.missing.info[/TD]
[/TR]
[TR]
[TD]Org 2[/TD]
[TD]not.in.db[/TD]
[/TR]
</tbody>[/TABLE]

As you see, there is a majority agreement (2/3) for Org 1, and no agreement (three different answers) for Org 2. Both columns contain strings. I know how to use EXACT, but I'm not sure how to do it in groups of three rows.

I need to find a way to tease out two things:

1. Which organizations were not agreed upon by a majority (contained three different answers)? I'll need to flag these for review.
2. When there was majority agreement, I need to create a list with the organization name and the correct status. It would be fantastic if I could also include all of the other data (not shown here) associated with each organization.

I've been using this board a lot lately, and always see great answers. This is one I couldn't find by searching. Thanks in advance for your help!

 

[rallcorn]

Welcome to the MrExcel message board.

Assuming row 1 is header row
Col A = Name
Col B = Status
Col ?? = Results (this is where we're putting the formula) beginning in row 2 of this column

=IF(COUNTIFS(A:A,A2,B:B,B2)=1,"Review","Majority")

Now, regarding pulling a list of the "Majority" organizations & consensus status:
You didn't say what kind of "other" data, but a quick way to compile a list would be to create a pivot table
Results go in Report Filter section (filter to "Majority")
In Row Labels section put Name & Status with NO subtotals

 

[oldbrewer]

[TABLE="width: 507"]
<colgroup><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NAME[/TD]
[TD]STATUS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 1[/TD]
[TD]active[/TD]
[TD="align: right"]1[/TD]
[TD]MAJ[/TD]
[TD]Org 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 1[/TD]
[TD]not.active[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 1[/TD]
[TD]not.active[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 2[/TD]
[TD]in.db[/TD]
[TD="align: right"]1[/TD]
[TD]NO MAJ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 2[/TD]
[TD]in.db.missing.info[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Org 2[/TD]
[TD]not.in.db[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
in F7 =COUNTIF(E$7:E$9,E7)
in g7
=IF(SUM(F7:F9)>3,"MAJ","NO MAJ")

in h7
=IF(G7="maj",D7,"")

F7 is the top 1 in the column 122111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]