Rearrange data in 3 columns

[Sinbad]

Hi,

need help figuring this out. I was given three documents with essentially the same data, but not all documents have all the entries, so I want to combine it into one complete list.

I have copied the data as is into column A, B and c. now I wish to consolidate them so I have one list with no duplicates.

I tried the remove duplicates feature, weirdly it tells me there are no duplicated, if I do duplicate highlighting though, the sheet lights up.

Any suggestions ?

Sample:

col a	col b	col c
Fanta	CabSouv	Bread
Milk 03	Soup	Fanta
Marmite	Apples	CabSouv
Soup	Fanta	Marmite

<tbody>
</tbody>

This should result in:

Fanta

Milk 03

Marmite

Soup

CabSouv

Apples

Bread

<tbody>
</tbody>

any simple way of doing this ?

Thank you.

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​
1​	Header1​	Header2​	Header3​		UniqueList​
2​	Fanta​	CabSouv​	Bread​		Fanta​
3​	Milk 03​	Soup​	Fanta​		CabSouv​
4​	Marmite​	Apples​	CabSouv​		Bread​
5​	Soup​	Fanta​	Marmite​		Milk 03​
6​					Soup​
7​					Marmite​
8​					Apples​
9​

Array formula in E2 copied down
=IFERROR(INDIRECT(TEXT(SMALL(IF(COUNTIF(E$1:E1,A$2:C$5)=0,ROW(A$2:C$5)*10^5+COLUMN(A$2:C$5)),1),"R0C00000"),0),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Mike Szczesny]

Can you use this?

=IFERROR(IFERROR(IFERROR(INDEX($A$1:$A$4,MATCH(0,COUNTIF($E$1:E1,$A$1:$A$4),0)),INDEX($B$1:$B$4,MATCH(0,COUNTIF($E$1:E1,$B$1:$B$4),0))),INDEX($C$1:$C$4,MATCH(0,COUNTIF($E$1:E1,$C$1:$C$4),0)))," ") Although it looks complicated, it just selects the unique items for each range. I use the last =iferror to remove errors. Use Cntrl+Shift+Enter Fanta CabSouv Bread Unique Milk 03 Soup Fanta Fanta Marmite Apples CabSouv Milk 03 Soup Fanta Marmite Marmite Soup CabSouv Apples Bread <colgroup><col span="2"><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Sinbad]

very interesting indeed. Thank you.