How to identify duplicate combinations of two values in random order

Question

I am trying to identify the second duplicate combinations of cells across two (or more) columns. Complicating matters, the order of combinations varies.

My data looks something like this:

aaa   111
222   aaa
111   aaa
111   bbb
bbb   111

I'm looking for a formula that, for this example, would tell me that rows 1, 2 and 4 are the first instances of the combined values, whereas rows 3 and 5 are the second instances.

Answer 1

This is not hard to do with functions/formulas if you’re dealing with only two columns.  You’ll need three “helper columns” in addition to a results column, and you’ll need to identify a string that never appears in your data.  Let’s use columns E through H, and the string /:

• E1 — =IF(A1<B1, A1, B1)
• F1 — =IF(A1<B1, B1, A1)
• G1 — =E1 & "/" & F1
• H1 — =COUNTIF(G$1:G1, G1)

E1 is the lesser (minimum) of A1 and B1, and F1 is the greater of A1 and B1.  In other words, E1 and F1 contain the values of A1 and B1, but sorted into ascending order.  Then G1 is E1 concatenated with F1, delimited with /.  This is a unique, order-independent combination of the values A1 and B1.  E.g., G1 and G3 both contain 111/aaa, because rows 1 and 3 have 111 and aaa in Columns A and B in some order.  Then Column H counts how many times this combination has appeared so far.

           

Note that H3 and H5 each contains 2, because rows 3 and 5 are the ones that have a combination for the second time.  When I repeated the row 1 data in row 6, I got H6 = 3, because that was the third time the combination of 111 and aaa appeared.

As long as the data can be numeric or textual, this gets very complicated very fast if you want to do more than two columns.  There’s a way to make it easier if the data are all numbers, however.  Or, if you’re willing to stipulate a maximum length for your data (e.g., nothing longer than three characters), it may become manageable.