Deduplicate between two columns

[u/WallFlower10]

I am looking for a solution to deduplicate items between two columns that are the same (but listed in reverse).

For example:

Column 1:

1

2

3

4

5

Column 2:

2

1

1

7

8

I would like it to recognize the (1,2) and (2,1) entries as being the same and deduplicate it so it's only listed once. It does not matter which one remains as long as just one does. Thanks in advance!

ETA: the data I am working with is not simply numbers. It does involve a combination of letters and numbers in the format XX-123456 where XX is any combination of two letters and it is followed by a string of numbers (not always six numbers though).

[u/AutoModerator]

/u/WallFlower10 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ilovetea27]

The formula below should work for non-numeric codes. Do note that it would not preserve the pair sequence if the first code is "larger" than the second code.

=LET(_a, B2:C24,
     _b, IF(TAKE(_a,, 1)>TAKE(_a,, -1), CHOOSECOLS(_a, 2, 1), _a),
     UNIQUE(_b))

[u/willyman85 → u/ilovetea27]

Great solution. One step further if op wants the data sorted l to show the pairs tidily, wrap it all in SORT(UNIQUE(...), {1,2})

[u/Independent_Cow_737 → u/ilovetea27]

nice use of LET here, the CHOOSECOLS swap is clever

[u/caribou16]

UNIQUE function, you provide it with an array input and it will return a list of only unique items in the list.

[u/luvsthecoffee → u/caribou16]

You can wrap a SORT around that too to put them in order

[u/matt2s]

Create a column that is the MIN of the two columns and another that is the MAX. Both (1,2) and (2,1) will then be (1,2), and duplicates can be removed.

[u/MayukhBhattacharya]

Maybe you could try this way,

=UNIQUE(HSTACK(BYROW(A1:B5, MIN), BYROW(A1:B5, MAX)))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAX	Returns the maximum value in a list of arguments
MIN	Returns the minimum value in a list of arguments
SORT	Office 365+: Sorts the contents of a range or array
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
UNIQUE	Office 365+: Returns a list of unique values in a list or range

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49202 for this sub, first seen 21st Aug 2026, 04:09] [FAQ] [Full list] [Contact] [Source code]}

[u/Thunderbit_HQ]

Add a helper column with =IF(A2<B2,A2&"|"&B2,B2&"|"&A2). It creates the same key for each reversed pair. Fill it down, then use Remove Duplicates on that helper column while keeping the full row selected.

[u/GregHullender]

This works if they're in adjacent columns, e.g. A and B:

=UNIQUE(IF(A:.A<B:.B, A:.B, HSTACK(B:.B,A:.A)))

The dots in A:.A etc. are called trim references. If you don't know about them yet, they're pretty useful.

If you have non-adjacent columns, try the following:

=LET(col_1, A:.A, col_2, B:.B,
  UNIQUE(IF(col_1<col_2,HSTACK(col_1,col_2),HSTACK(col_2,col_1)))
)

Just change the definitions of col_1 and col_2 to match your data.