Rearrange data in 3 columns

Sinbad

Board Regular
Joined
Apr 18, 2012
Messages
224
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,226,735
Messages
6,192,732
Members
453,752
Latest member
Austin2222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top