# How to identify duplicate combinations of two values in random order

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.

• "I'm looking for a formula..." - I'm no expert, but you may need something like VBA to achieve this; you might get a kindly soul answering here on SU, or over at SO :-) – bertieb Aug 11 '15 at 5:10

## 1 Answer

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.