Combine values from two columns to produce unique value list

Hi,

Loooking for some help with this please...

I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).

Any ideas how I can do this please using a formula?

Many thanks.

unique list.xls

hi lucasreece. maybe a helper column in D2 copied down:
=B2&" - "&C2

then this array formula in E2:
=IF(ISNA(MATCH(0,COUNTIF(E$1:E1,$D$2:$D$10),0)),"",INDEX($D$2:$D$10,MATCH(0,COUNTIF(E$1:E1,$D$2:$D$10),0)))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

well not really i think but i maybe wrong, however to avoid that array formula
d2 down
=IF(SUMPRODUCT(--($B$2:B2&$C$2:C2=B2&C2))=1,ROWS($A$1:A1),"")
e2 down
=IF(ROWS($A$1:A1)>COUNT($D$2:$D$200)-1,"",(INDEX($B$2:$B$200,MATCH(SMALL($D$2:$D$200,ROWS($A$1:A1)),$D$2:$D$200,0))&"-"&INDEX($C$2:$C$200,MATCH(SMALL($D$2:$D$200,ROWS($A$1:A1)),$D$2:$D$200,0))))

Enter this formula in E2 and copy down:

Formula:

Please Login or Register  to view this content.

Enter this formula in F2 and copy down:

Formula:

Please Login or Register  to view this content.

This keeps all given names in one column and all surnames in a matching column. to produce a two column listing of unique values.

If you want both surname and given name in one cell use:

Formula:

Please Login or Register  to view this content.

@newdoverman op seems to have 2003 plus what happens if 2 or more people have the same firstname?

A third criteria would be required to make the distinctions between names. An ID # for each individual would be ideal but barring that the more criteria you have for identification, the better off you are. If there was an Initial or second name, that would also help.

The answer given solves the problem as stated. If there are other complications, they should have been stated in the original problem.

Having said that. To bring in the surname first enter this in F2 and copy down:

Formula:

Please Login or Register  to view this content.

Enter this in E2 and copy down to get the given names:

Formula:

Please Login or Register  to view this content.

ah ah but what about

Good point!

Sometime I just get stuck in one way of looking at a problem and try to make it work. Your SUMPRODUCT formula got me thinking (after a while) "what was I thinking???" I was trying to reduce a list using a formula and couldn't get the method of what I was doing out of my head.

This is what I really wanted to do but couldn't until you "kicked" my brain out of it's rut.

I couldn't combine the columns in order to count the duplicates, completely forgetting about SUMPRODUCT and it took a while even after you pointed it out in your examples. Once that was overcome the rest was easy. Just choose if the results would be in 1 column (combined formulae) or in two columns with a separate formula in each.

Crumb!! Getting old is no fun