Extracting all combinations of two columns

[IanC]

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!

 

[eliW]

on 3rd column concatenate column1 and column2
make Advanced filtering on column 3 checking "unique records"
Eli

 

[IanC]

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.

 

[fairwinds]

Hi,

The formula in D colum is to be entered with Ctrl + Shift + Enter.

Book1
	A	B	C	D	E	F	G	H
1
2	Stan	28		1		Stan	28
3	Bob	11		2		Bob	11
4	Stan	12		3		Stan	12
5	Bob	11		3		Dorris	14
6	Stan	28		3
7	Bob	11		3
8	Dorris	14		4
9
Sheet1

 

[just_jon]

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]

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]

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)

 

[jlkirk]

Thanks Richard. Yes, I figured that out!