Look for each value in one column and match across multiple columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Missed that part of the requirement, try this - same general premise except looks across all records instead of just record by record
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This worked great. Thanks for the help.
