Compare 2 columns and remove duplicates

[viper20]

Hello,

I have 2 columns A and B column A has about 700 rows and column B has about 400 rows. Some of the data appears in both column A and B so what i want to do is take any of the data that are not duplicates and move them to column C, the data in the 2 fields does not directly match up from left to right not sure if i explained that right but i will provide an example to demonstrate what i mean:

Column A Column B

apple watermelon
orange peaches
grape apple
watermelon kiwi
blueberries
strawberries
mango
peaches
kiwi
pineapple

So how can i get the unique fields from column A to appear in Column C?

Thanks guys

 

[Sheripres]

You can take column A and copy it to column C, then, take column B and copy it to the end of the data in Column C. You now have the data from Column A and B in one long string in column C. If you have 2007 Excel, there is a button that allows you to "remove duplicates". If you are still in 2003 (like I am), Data Sort on column C and in Column D, type the following formula: =if(C1=C2,1,0). If you have a header, the formula would drop down one row. Copy, Paste Special, Values so that the formula is now hard coded to show either "1" or "0". Data Sort on Column D and remove all the "1's".

Hope this helps!

 

[Yahya]

May Be this will help
this is your data
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">apple</td><td style=";">watermelon</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">orange</td><td style=";">peaches</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">grape</td><td style=";">apple</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">watermelon</td><td style=";">kiwi</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">blueberries </td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">strawberries</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">mango</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">peaches</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">kiwi</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">pineapple</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

after run this code

Sub Test()
Dim c As Range, d As Range, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range("A1:A" & LR)
    If Not WorksheetFunction.CountIf(Range("B1:B" & LR), c.Value) >= 1 Then
        i = i + 1
        Range("C" & i).Value = c.Value
    End If
Next c

End Sub

the result will be
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">apple</td><td style=";">watermelon</td><td style=";">orange</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">orange</td><td style=";">peaches</td><td style=";">grape</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">grape</td><td style=";">apple</td><td style=";">blueberries </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">watermelon</td><td style=";">kiwi</td><td style=";">strawberries</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">blueberries </td><td style="text-align: right;;"></td><td style=";">mango</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">strawberries</td><td style="text-align: right;;"></td><td style=";">pineapple</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">mango</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">peaches</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">kiwi</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">pineapple</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[viper20]

Hey Yahya,

This is exactly what i am looking to do stupid question where do i run that code you provided?

Thanks

 

[viper20]

Nevermind i got the macro recorded and it works great

Thank you so much!

 

[Yahya]

you are welcome
============
and if you care
you can do it by formula

Test It Please
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">apple</td><td style=";">watermelon</td><td style=";">orange</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">orange</td><td style=";">peaches</td><td style=";">grape</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">grape</td><td style=";">apple</td><td style=";">blueberries </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">watermelon</td><td style=";">kiwi</td><td style=";">strawberries</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">blueberries </td><td style="text-align: right;;"></td><td style=";">mango</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">strawberries</td><td style="text-align: right;;"></td><td style=";">pineapple</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">mango</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">peaches</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">kiwi</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">pineapple</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$1:$A$10,SMALL(<font color="Green">IF(<font color="Purple">ISNA(<font color="Teal">MATCH(<font color="#FF00FF">$A$1:$A$10,$B$1:$B$4,0</font>)</font>),ROW(<font color="Teal">$A$1:$A$10</font>)-ROW(<font color="Teal">$A$1</font>)+1</font>),ROW(<font color="Purple">1:1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[petes]

This code is not working when the data is like this (See below table). My expectation is 1103 & 1106 should not be pulled in Column C since it exists in both.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[/TR]
[TR]
[TD]1102[/TD]
[TD]7013[/TD]
[TD]1103[/TD]
[/TR]
[TR]
[TD]1103[/TD]
[TD]1101[/TD]
[TD]1106[/TD]
[/TR]
[TR]
[TD]1104[/TD]
[TD]1102[/TD]
[TD]1110[/TD]
[/TR]
[TR]
[TD]1105[/TD]
[TD]1104[/TD]
[TD]1153[/TD]
[/TR]
[TR]
[TD]1106[/TD]
[TD]1105[/TD]
[TD]1188[/TD]
[/TR]
[TR]
[TD]1107[/TD]
[TD]1107[/TD]
[TD]1189[/TD]
[/TR]
[TR]
[TD]1110[/TD]
[TD]2417[/TD]
[TD]1199[/TD]
[/TR]
[TR]
[TD]1153[/TD]
[TD]2485[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1188[/TD]
[TD]3618[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1189[/TD]
[TD]3649[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1199[/TD]
[TD]5129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1103[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1105[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1106[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


How can I revise the code?

Thanks!

 

[sandy666]

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"ColumnA"},Table10,{"ColumnB"},"Table10",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Table10"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ColumnA[/td][td=bgcolor:#5B9BD5]ColumnB[/td][td][/td][td=bgcolor:#70AD47]ColumnA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1102​

[/td][td=bgcolor:#DDEBF7]

7013​

[/td][td][/td][td=bgcolor:#E2EFDA]

1110​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1103​

[/td][td]

1101​

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

1153​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1104​

[/td][td=bgcolor:#DDEBF7]

1102​

[/td][td][/td][td=bgcolor:#E2EFDA]

1188​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1105​

[/td][td]

1104​

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

1189​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1106​

[/td][td=bgcolor:#DDEBF7]

1105​

[/td][td][/td][td=bgcolor:#E2EFDA]

1199​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1107​

[/td][td]

1107​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1110​

[/td][td=bgcolor:#DDEBF7]

2417​

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

[tr=bgcolor:#FFFFFF][td]

1153​

[/td][td]

2485​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1188​

[/td][td=bgcolor:#DDEBF7]

3618​

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

[tr=bgcolor:#FFFFFF][td]

1189​

[/td][td]

3649​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1199​

[/td][td=bgcolor:#DDEBF7]

5129​

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

[tr=bgcolor:#FFFFFF][td][/td][td]

1102​

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

[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#DDEBF7]

1103​

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

[tr=bgcolor:#FFFFFF][td][/td][td]

1104​

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

[tr=bgcolor:#FFFFFF][td][/td][td=bgcolor:#DDEBF7]

1105​

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

[tr=bgcolor:#FFFFFF][td][/td][td]

1106​

[/td][td][/td][td][/td][/tr]
[/table]

 

[petes]

Thanks for Power query suggestion, but actually I need a macro to achieve this.
One more thing what i noticed is, this is happening when the data in column B exceeds column B

 

[Peter_SSs]

One more thing what i noticed is, this is happening when the data in column B exceeds column B

If you look back to the original question, one of the specifications was that column A had more data than column B.

If your data always has column B longer than column A, you could just swap all column references in the code. Is that your circumstance or can either column be the longer one?

Also, in that original sample data, all the values in the shorter list appeared in the longer list. Is that your circumstance too or can either column contain unique entries?