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

 

[Akuini]

Yes, but please reply in this thread not through PM. So what new problems do you have?

 

[hafe4k]

Thanks Akuini, just wanted a slight amendment, i am wanting to see the unique fields from each column in a separate column?
So in our example:

Column A	Column B	Column C	Column D
Alpha	Alpha	Romeo	India
Bravo	Bravo	Zulu
Romeo	Golf
Golf	India
Zulu

<tbody>
</tbody>

 

[Akuini]

Ok, try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1082893b()
[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], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] x, va
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/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] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR]
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        
        x = Trim(va(i, j))
        [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] d.Exists(x) [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]If[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] d(x) = [COLOR=crimson]1[/COLOR]
            [COLOR=Royalblue]If[/COLOR] j = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] d(x) = [COLOR=crimson]2[/COLOR]
            [COLOR=Royalblue]Else[/COLOR]
            d(x) = [COLOR=crimson]3[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
        
    [COLOR=Royalblue]Next[/COLOR]
 [COLOR=Royalblue]Next[/COLOR]
 
[COLOR=Royalblue]If[/COLOR] d.Exists([COLOR=brown]""[/COLOR]) [COLOR=Royalblue]Then[/COLOR] d.Remove [COLOR=brown]""[/COLOR]
i = [COLOR=crimson]0[/COLOR]: j = [COLOR=crimson]0[/COLOR]

Range([COLOR=brown]"C1:D"[/COLOR] & rr).ClearContents
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x [COLOR=Royalblue]In[/COLOR] d
        [COLOR=Royalblue]If[/COLOR] d(x) = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]Then[/COLOR] i = i + [COLOR=crimson]1[/COLOR]: Cells(i, [COLOR=brown]"C"[/COLOR]) = x
        [COLOR=Royalblue]If[/COLOR] d(x) = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]Then[/COLOR] j = j + [COLOR=crimson]1[/COLOR]: Cells(j, [COLOR=brown]"D"[/COLOR]) = x
    [COLOR=Royalblue]Next[/COLOR]
    
Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[hafe4k]

Thanks as always, much appreciated looks to be working as expected.

 

[hafe4k]

Hi guys - was wondering if some advise or help please -
I'm working on a sheet - which will have data imports every month.
However when I import the new data is't important its in the same list view as the previous months - if things have moved I need to know so I can move the matched data from previous months accordingly so all still in sync?

How would I best run something which would tell me In Column C that whats in A & B are the same?

Would something like this work - any assistance with a query please? Or any better way of showing it?

Cell	Column A	Column B	Look at Column B and Compare positioning with A
1	Alpha	Alpha	'Alpha' match same row
2	Bravo	Bravo	'Bravo' match same row
3	Charlie	Golf	'Charlie' removed - 'Golf' moved 'B3'
4	Golf	Quebec	'Golf' moved 'B3' - 'Quebec' moved 'B4'
5	Papa	November	'Papa' removed - 'November' NEW
6	Quebec	Hotel	'Quebec' moved B4 - 'Hotel' NEW
7	Zulu	Zulu	'Zulu' match same row