Extracting all combinations of two columns

IanC

New Member
Joined
Jan 21, 2004
Messages
12
I have a list consisting of two columns which may contain duplicate data, but I need to pull out all unique entries across the two columns.

For example if I have...


  • Stan 28
    Bob 11
    Stan 12
    Bob 11
    Stan 28
    Bob 11
    Dorris 14

I only want to see the unique combinations of the two colums in another list...

  • Stan 28
    Bob 11
    Stan 12
    Dorris 14

Probaly very simple, but not too sure how to do this?

Thanks in advance!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
on 3rd column concatenate column1 and column2
make Advanced filtering on column 3 checking "unique records"
Eli
 

IanC

New Member
Joined
Jan 21, 2004
Messages
12
Maybe I wasn't totally clear.

I need the output to be in two columns. There is no way to 'unconcatenate' the data using the above approach.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

IanC said:
Maybe I wasn't totally clear.

I need the output to be in two columns. There is no way to 'unconcatenate' the data using the above approach.

If you'd concatenate the data as in --

=A1&"~"&B1

where the tilde -- or some other -- character did not exist in A or B, then you can always use Data | Text-to-columns to re-split the 3rd column back into its original parts.
 

jlkirk

Active Member
Joined
May 6, 2002
Messages
304
Fairwinds,
I have a similar issue with two columns with over 300 rows of data. Should this work as well? I have tried plugging in your formula, but get #### or #VALUE messages.
Thanks.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
There are 3 distinct formulas that Fairwinds has used there:

one in D2, one in F2 and one in G2 - you need to use the drop down arrow in the top left of the table to view each formula in turn. Note, the formula in D2 is an array formula and so must be confirmed with Ctrl+Shift+Enter (not just Enter)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,883
Messages
5,766,911
Members
425,385
Latest member
djkevnic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top