Find majority match in groups of cells

nosila

New Member
Joined
Apr 23, 2013
Messages
1
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:

NAMESTATUS
Org 1active
Org 1not.active
Org 1not.active
Org 2in.db
Org 2in.db.missing.info
Org 2not.in.db

<tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

rallcorn

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
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

Board Regular
Joined
Apr 11, 2010
Messages
11,005
NAMESTATUS
Org 1active1MAJOrg 1
Org 1not.active2
Org 1not.active2
Org 2in.db1NO MAJ
Org 2in.db.missing.info1
Org 2not.in.db1

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

<colgroup><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top