Remove duplicate from list

[hafe4k]

Hi there,

I have 2 columns A and B column A has about 50 rows and column B has about 50 rows too. Some of the data appears in both column A and B, my aim is to get 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 and things in column a may be unique but things in column b may be unique too.

Column A | Column B | Column C
----------------------------------------------------
Alpha |Alpha | India
Bravo |Bravo | Romeo
Golf |Golf | Zulu
Romeo |India
Zulu

So how can I get only the unique fields from column A and Column B to appear in Column C?

Thanks guys

 

[GR00007]

If there's no relationship between columns A & B they could both be copied into column C, highlight and remove duplicates.
The example doesn't give much to go on.

 

[sandy666]

if you're able to use PowerQuery you can try this

excel file: stay with non-duplicated values

in short:
define name for this range (here: tbl_src)
use it as source in PQEditor
duplicate table
merge first table itself but with different columns by only matching rows
duplicated table : use Table.ToColumns(), filter by null
merge tables by Right Anti
Load to the sheet

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Alpha [/td][td]Alpha [/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bravo [/td][td]Bravo [/td][td][/td][td=bgcolor:#E2EFDA]Romeo [/td][/tr]

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

[tr=bgcolor:#FFFFFF][td]Romeo [/td][td]India[/td][td][/td][td=bgcolor:#E2EFDA]India[/td][/tr]

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

 

[hafe4k]

Many thanks for the responses.

Sandy that is exactly what I want to do but unfortunately I don’t know PowerQuery. Is there a script I can run in VBA to do this?

 

[sandy666]

If you've PowerQuery installed (excel 2010/2013) or built-in (2016 and higher) you can see all steps in PQ Editor on the right side (step by step) in the linked file.
If you really don't know how to do it I can prepare a little movie for you when I've a moment.

 

[hafe4k]

Thanks Sandy - i'm running Office 2013 hence this will require me to install something. Group policies in place at work won't allow this and as I need to compare data on an ongoing basis so no option to take home unfortunately.
I came across this post:
https://www.mrexcel.com/forum/excel-questions/577145-compare-2-columns-remove-duplicates.html - Post 3

which seems to do what I want but the script only looks at Column A not Column B so in our example it would move over Romeo and Zulu but not India.
Is there an amendment to the VB script that would alleviate this?

 

[Akuini]

Thanks Sandy - i'm running Office 2013 hence this will require me to install something. Group policies in place at work won't allow this and as I need to compare data on an ongoing basis so no option to take home unfortunately.
I came across this post:
https://www.mrexcel.com/forum/excel-questions/577145-compare-2-columns-remove-duplicates.html - Post 3

which seems to do what I want but the script only looks at Column A not Column B so in our example it would move over Romeo and Zulu but not India.
Is there an amendment to the VB script that would alleviate this?

Hi, hafe4k
Try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1082893a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082893-remove-duplicate-list.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] rr [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

rr = Range([COLOR=brown]"A:B"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    va = Range([COLOR=brown]"A1:B"[/COLOR] & rr)
        [COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
            d.CompareMode = vbTextCompare
    
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x [COLOR=Royalblue]In[/COLOR] va
        x = Trim(x)
        [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] d.Exists(x) [COLOR=Royalblue]Then[/COLOR]
            d(x) = [COLOR=crimson]1[/COLOR]
            [COLOR=Royalblue]Else[/COLOR]
            d(x) = [COLOR=crimson]2[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
  
[COLOR=Royalblue]If[/COLOR] d.Exists([COLOR=brown]""[/COLOR]) [COLOR=Royalblue]Then[/COLOR] d.Remove [COLOR=brown]""[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x [COLOR=Royalblue]In[/COLOR] d
        [COLOR=Royalblue]If[/COLOR] d(x) = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] d.Remove x
    [COLOR=Royalblue]Next[/COLOR]
    
Range([COLOR=brown]"C1"[/COLOR]).Resize(d.count, [COLOR=crimson]1[/COLOR]) = Application.Transpose(Array(d.Keys))
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[hafe4k]

Thanks Akuini, absolute legend!!
Much appreciated. Works perfectly.

 

[Akuini]

Your welcome & thanks for the reply

 

[hafe4k]

Sorry Akuini, don't think I can receive your reply in PM.. did you receive the message i sent?

Thanks