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

[bcfields2410]

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.

[Luke_C]

Hi @bcfields2410 

Here's how I'd approach this:

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

[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 · Accepted solution]

Hi @bcfields2410 

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

[bcfields2410]

This worked great. Thanks for the help.