CODEWITHSUNDEEP
excelexcel-formuladuplicates
F Bert
F Bert

Reputation: 81

Excel - identify duplicates consisting of two columns, in changing order

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

To illustrate - 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.

Upvotes: 3

Views: 562

Answers (1)

lc.
lc.

Reputation: 116538

If you don't mind adding another column to the sheet, you can create an "index" for each row, making sure you always list the columns in the same order. (You can always copy values and delete the intermediary column.)

For example the following formula:

=IF($A1<$B1,CONCATENATE($A1,"!",$B1),CONCATENATE($B1,"!",$A1))

Will create a unique identifier like the following, using a ! character for a delimiter:

aaa 111 111!aaa
222 aaa 222!aaa
111 aaa 111!aaa
111 bbb 111!bbb
bbb 111 111!bbb

Then it's just a matter of using COUNTIF, for example with the index in column C:

=COUNTIF($C$1:$C1,$C1)

This will produce the following sheet, the number being the ordinal 1-based count of how many times that pair has appeared.

aaa 111 111!aaa 1
222 aaa 222!aaa 1
111 aaa 111!aaa 2
111 bbb 111!bbb 1
bbb 111 111!bbb 2

Someone with more time on their hands can probably come up with an array formula to do the same thing in memory.

Upvotes: 2

Related Questions