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:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]col a
[/TD]
[TD]col b
[/TD]
[TD]col c
[/TD]
[/TR]
[TR]
[TD]Fanta
[/TD]
[TD]CabSouv
[/TD]
[TD]Bread
[/TD]
[/TR]
[TR]
[TD]Milk 03
[/TD]
[TD]Soup
[/TD]
[TD]Fanta
[/TD]
[/TR]
[TR]
[TD]Marmite
[/TD]
[TD]Apples
[/TD]
[TD]CabSouv
[/TD]
[/TR]
[TR]
[TD]Soup
[/TD]
[TD]Fanta
[/TD]
[TD]Marmite
[/TD]
[/TR]
</tbody>[/TABLE]


This should result in:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Fanta
[/TD]
[/TR]
[TR]
[TD]Milk 03
[/TD]
[/TR]
[TR]
[TD]Marmite
[/TD]
[/TR]
[TR]
[TD]Soup
[/TD]
[/TR]
[TR]
[TD]CabSouv
[/TD]
[/TR]
[TR]
[TD]Apples
[/TD]
[/TR]
[TR]
[TD]Bread
[/TD]
[/TR]
</tbody>[/TABLE]

any simple way of doing this ?

Thank you.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Header1​

[/td][td]

Header2​

[/td][td]

Header3​

[/td][td][/td][td]

UniqueList​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Fanta​

[/td][td]

CabSouv​

[/td][td]

Bread​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Fanta​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Milk 03​

[/td][td]

Soup​

[/td][td]

Fanta​

[/td][td][/td][td="bgcolor:#D9D9D9"]

CabSouv​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Marmite​

[/td][td]

Apples​

[/td][td]

CabSouv​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Bread​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Soup​

[/td][td]

Fanta​

[/td][td]

Marmite​

[/td][td][/td][td="bgcolor:#D9D9D9"]

Milk 03​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Soup​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Marmite​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Apples​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"][/td][/tr]
[/table]

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? [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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

[TABLE="width: 335"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Fanta[/TD]
[TD]CabSouv[/TD]
[TD]Bread[/TD]
[TD][/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Milk 03[/TD]
[TD]Soup[/TD]
[TD]Fanta[/TD]
[TD][/TD]
[TD]Fanta[/TD]
[/TR]
[TR]
[TD]Marmite[/TD]
[TD]Apples[/TD]
[TD]CabSouv[/TD]
[TD][/TD]
[TD]Milk 03[/TD]
[/TR]
[TR]
[TD]Soup[/TD]
[TD]Fanta[/TD]
[TD]Marmite[/TD]
[TD][/TD]
[TD]Marmite[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Soup[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CabSouv[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bread[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sinbad]

very interesting indeed. Thank you.