Look for each value in one column and match across multiple columns

Hi,

I couldn't find a solution to my problem and wanted to see if anyone could help. I have about 300k rows of data or else I'd do this in Excel. I am trying to look to see if ANY of the data in Group1 also shows in any of the other Group columns. Then I'd like to return "Yes" and the highest group for any of the rows it was matched in. So for below Group1 200 should return Yes in a column and also Group 3 in another column. Group1 100 should also return "Yes" and the highest group for the rows it was matched which is Group4. Group1 600 should say "No". Hope this makes sense.

Group1	Group2	Group3	Group4	Group5
100	100
200	100	200
400	400	400	400
500	500	500	500	500
600
700	750	800	100
1000
1200
1400	1000

Thanks for taking a look.

Workflow

Accepted answers

Luke_C

Hi @bcfields2410

Missed that part of the requirement, try this - same general premise except looks across all records instead of just record by record

New Workflow6.yxmd

All comments

Luke_C

Hi @bcfields2410

Here's how I'd approach this:

Add unique record ID
Transpose groups 2-5
Filter to records where group 1 = value (transposed)
Summarize to get the last value for each record ID (highest group)
Add matched field
Join back to original data
union any fall outs

New Workflow6.yxmd

bcfields2410

Thanks for the reply

This is a good start, but Group1 100 should say Yes (which it does) and Group 4 being the highest as it is located in Group4 on record six from your example. This is the odd part of the data set that I was having issues figuring out.

Luke_C

Hi @bcfields2410

Missed that part of the requirement, try this - same general premise except looks across all records instead of just record by record

New Workflow6.yxmd

bcfields2410

This worked great. Thanks for the help.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703