Excel - find unique combinations regardless of order
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I have an excel sheet like this GD https://docs.google.com/spreadsheets/d/1ckjDmXRE1TnDPSg7GGTqV4cA79v0hnq2elxx4Eeq4ks/edit?usp=sharing

as you will see we have some tags in 3 columns, however sometimes the order of the tags varies but materially we know they are the same . eg order doesn't matter. I was thinking of an elegant solution to the issue and was wondering if someone can come up with a way maybe to convert the values of the cells to a numeric representation and then add the values from the 3 columns together, we could then find matches based on the numeric representation?

This bounty is for a working excel file that will yield a solution to finding like rows regardless of tag order.

awarded to kerncy
Tags
excel

Crowdsource coding tasks.

2 Solutions


Hello Qdev,

here's my solution: https://docs.google.com/spreadsheets/d/18o5aYRD-JyvkOmzJrh272qmO-buQlwltQoVJK_avd4k/edit

I made new sheet with all unique tags and gave them ID, which is then added together to create unique ID for each tag combination.

Let me know if this solves your problem, thank you!

Winning solution

Hi,

it can be a little bit "archaïc" but you can do it using only excel function. In a new column (on line 2) add this function :

=IF(AND(A2>B2;B2>C2);A2&"-"&B2&"-"&C2;IF(AND(A2>C2;C2>B2);A2&"-"&C2&"-"&B2;IF(AND(B2>A2;A2>C2);B2&"-"&A2&"-"&C2;IF(AND(B2>C2;C2>A2);B2&"-"&C2&"-"&A2;IF(AND(C2>A2;A2>B2);C2&"-"&A2&"-"&B2;C2&"-"&B2&"-"&A2)))))

It will sort your column A, B and C to generate a unique ordered tag. Than duplicate this code on all your lines, you will get a tag that can be the same on many lines.

A working example of generation can be found here :
https://docs.google.com/spreadsheets/d/1QF-b6aBMbb3hBZ0YH3VmgHf7Uq2K3TrYsxx5Twq1qMo/edit?usp=sharing

The positive point with this solution, is that you can read your "tag match" directly without thinking of which numeric value the tag was attached

Best regards

View Timeline