Finding match of two sets of data

[Gman33]

Hello everyone,

I am having a problem and I'm a newbie with excel so looking for some help!

I need to compare two sets of data and find a match, for example...

I need to compare User 1 Row with each Row of Group B to see if User 1 picked User 6 and User 6 also picked User 1 for a positive match. This needs to be done for all Group A users to all Group B users. If a match for Pick1, then "match pick 1" would display "match w/ user#"

[TABLE="width: 200"]
<tbody>[TR]
[TD]Group A[/TD]
[TD]Pick1[/TD]
[TD]Pick2[/TD]
[TD]Match Pick 1[/TD]
[TD]Match Pick 2[/TD]
[/TR]
[TR]
[TD]User:1[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:2[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:3[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:4[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:5[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:7[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:9[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]User:10[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you,
Gman

 

[DRSteele]

Welcome to the forum.

If I understood correctly, this formula will report the phrase "Match User # X" when the choice by a Group A User matches the Choice of the Group B User.

I altered the data to simplify user numbers. Copy D2 across and down as necessary.

	A	B	C	D	E
Pick1	Pick2	Match Pick 1	Match Pick 2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Group A User[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #E2EFDA"]Match User# 6[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FCE4D6"]Group B User[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet36​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(INDEX(B$8:B$12,MATCH(B2,$A$8:$A$12,0))=$A2,"Match User# "&B2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gman33]

Thank you, I will check it out when I get home!

 

[Gman33]

Welcome to the forum.

If I understood correctly, this formula will report the phrase "Match User # X" when the choice by a Group A User matches the Choice of the Group B User.

I altered the data to simplify user numbers. Copy D2 across and down as necessary.

	A	B	C	D	E
Pick1	Pick2	Match Pick 1	Match Pick 2

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Group A User[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #E2EFDA"]Match User# 6[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FCE4D6"]Group B User[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet36​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(INDEX(B$8:B$12,MATCH(B2,$A$8:$A$12,0))=$A2,"Match User# "&B2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This did not work. Also it is static and doesn't roll down the list to check for matches.

 

[DRSteele]

What?

There is only one match in your sample data. The other green cells display blank because there are no matches.

 

[Gman33]

Ok must be user error on my part then, thanks

 

[DRSteele]

Perhaps you could post some more sample data together with the expected results for each datum. We can dig into it until your objectives are met.