remove duplicates

[jammoca]

I have three lists .. A1:A10, B1:B10, C1:C10.

In these lists are names. Some names may appear in more than one list.

I'd like a way to finish with a list that only contains non-duplicates.

Can this be done without code?

If not, what code would be necessary ?

Chris

 

[drikuslee]

Click on the data Tab
Click Remove Duplicates

 

[jammoca]

ta for that, but unless I'm doing something wrong, it only seems to remove duplicates if the duplicates occur in the same column.

If I have for example, the following ....

ColA ColB ColC
harry aardvark ant
cathy banana bee
bill cathy cat
bonny dog dog
florence elephant elephant
chris florence giraffe
anastasia geebung hound
philip hislop insect
gerry idiot jerrycan
homer jamieson kite

then I'd like to return just the one list ...

harry
cathy
bill
bonny
florence
chris
anastasia
philip
gerry
homer
aardvark
banana
dog
elephant
geebung
hislop
idiot
jamieson
ant
bee
cat
giraffe
hound
insect
jerrycan
kite

notice, the duplicates of cathy, florence, dog, elephant have been removed.

Is this possible

 

[Peter_SSs]

I have three lists .. A1:A10, B1:B10, C1:C10.

In these lists are names. Some names may appear in more than one list.

I'd like a way to finish with a list that only contains non-duplicates.

Can this be done without code?

If not, what code would be necessary ?

Chris

It can be done without code by copying the 3 columns into a single column of 30 cells and then using Remove Duplicates as suggested by drikuslee (provided you are using Excel 2007+).

If you are using Excel 2003- it still could be done manually but a few more steps would be required.


Alteratively you can use the method suggested by mirabeau here (with adjustments to the ranges).

 

[Yahya]

try this formula from bosco_yip

Excel Workbook
	A	B	C	D	E
1	harry	aardvark	ant
2	cathy	banana	bee		harry
3	bill	cathy	cat		aardvark
4	bonny	dog	dog		ant
5	florence	elephant	elephant		cathy
6	chris	florence	giraffe		banana
7	anastasia	geebung	hound		bee
8	philip	hislop	insect		bill
9	gerry	idiot	jerrycan		cat
10	homer	jamieson	kite		bonny
11					dog
12					florence
13					elephant
14					chris
15					giraffe
16					anastasia
17					geebung
18					hound
19					philip
20					hislop
21					insect
22					gerry
23					idiot
24					jerrycan
25					homer
26					jamieson
27					kite
28
29
30
Sheet8

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[brettdj]

You can use my duplicate master addin to generate a unique list, http://www.experts-exchange.com/A_2123.html. It does provide additional optionality to formulae approaches in that
- it can return true uniques (only occuring once) or each variable that occurs at least onc
- runs over mutiple sheets if needed
- case insensitive/white space insensitive/regex replacement if necessary

Cheers
Dave