Compare 2 columns of data and get unique values in the 3rd column

Hi,

I have 2 long lists (up to 10,000 lines each) of serial numbers in 2 different worksheets. The numbers can have duplicate in the same column and also in the other worksheet. I would like to have a VBA code to extract all distinct values from both lists in the new worksheet and have them sorted in ascending order. Have tried with INDEX formula but it makes the workbook working very slow as there are too many records.

Thanks for any support,
Eduard

Have you tried removeduplicates on excel? (not VBA)

Nope. The idea is that the lists might change and I don't want to update manually the resulting list all the time.

INDEX formula but it makes the workbook working very slow as there are too many records
If you are comparing two columns, how is the Index makes so slow,it is a formula after all?

Well, even if you use vba, you'll use the removeduplicates method.

Anyway, the code is below:

'add new sheet where the two lists will be merged
Sheets.add.name="MyNewSheet"

'copy and paste the first list
Sheets("MySheet1").Range("MyRange1").copy
Sheets("MyNewSheet").activate
Celsl(1,1).PasteSpecial xlPasteValues

'determine the last row in the new sheet
FinalRow = Cells(rows.count,1).end(xlUp)

'copy-paste second list
Sheets("MySheet1").Range("MyRange1").copy
Sheets("MyNewSheet").activate
Cells(FinalRow,1).PasteSpecial xlPasteValues

'determine the last row in the new sheet, again
FinalRow = Cells(rows.count,1).end(xlUp)

'Remove duplicates
Sheets("MyNewSheet").activate
Range(Cells(1,1), Cells(FinalRow,1)).RemoveDuplicates Columns:=1

'determine the last row in the new sheet, again
FinalRow = Cells(rows.count,1).end(xlUp)

'Sort
Range(Cells(1, 1), Cells(FinalRow, 1)).Sort Key1:=Range(Cells(1, 1), Cells(FinalRow, 1)), Order1:=xlAscending, Header:=xlno

It's a complex array formula and it took me almost 10 min to extract 50 first records. This is the INDEX FORMULA I used:
=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "")

So it's obviously not a solution for me.

I guess that's all can do :/

It can be done with VBA, but need a sample for testing and to see the format of your data.

Thanks AB33. What do you mean by VBA? What you gave me is a VBA code. Or am I supposed to use it differently?

I think along the same lines as Mayda89.
Copy both ranges under one another (Mayda89 gave the code) and remove the duplicates
This code should work both in 2003 and 2007.
However, I have not tested it on such a large range.
Try it on a copy of your workbook if you want to try it.

Sub DeleteDups() 
     
    Dim x               As Long 
    Dim LastRow         As Long 
     
    LastRow = Cells(Rows.Count,1).End(xlUp).Row 
    For x = LastRow To 1 Step -1 
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then 
            Range("A" & x).Delete xlShiftUp

        End If 
    Next x 
     
End Sub

In Mayda89's code, I think you need to change a few things.

FinalRow = Cells(rows.count,1).end(xlUp)

Should be

FinalRow = Cells(rows.count,1).end(xlUp).Row

and the pasting should have an offset(1) included so as not to paste over the last value

Eduard,
I thought you wanted a macro(code) solution.

Indeed. For me VBA and macro is the same thing. Am I wrong in assuming that?

Okay! Let's put aside the teki side of VBA. Could you please attach sample book?

Jolivanes, thank you for the correction!
You're obviously right; I must have missed it because I just quickly wrote it here and didn't test it!

Cheers,

Attached is a sample. The idea is that whenever the values change in A1 or C1 in Distinct worksheet the S/N column to display only distinct values between the respective worksheets.

Thanks once again for any support.

Eduard

Eduard ,
Very tricky one!
How do these values in A1 and C1 relate to S/N values in each sheet?

Hi AB33,

A1 & C1 are basically the names of the worksheets which I want to compare and get the distinct values of S/N in Distinct worksheet.

Eduard

Eduard ,
Am I right, you want to compare column B of Jan and Feb sheets and copy the unique values in to sheet Distinct worksheet?

Try this one

Sub Eduard()
Dim x, i&, ws As Worksheet

With CreateObject("Scripting.Dictionary")
    .comparemode = 1
       For Each ws In Worksheets
            If ws.Name <> "Mar" And ws.Name <> "Distinct_List" Then
               x = ws.Range("B4").CurrentRegion
              For i = 2 To UBound(x)
                If Not .exists(x(i, 1)) Then .Item(x(i, 1)) = 1
              Next i
            End If
       Next
   Sheets("Distinct_List").Range("B4").Resize(.Count).Value = WorksheetFunction.Transpose(.Keys)
   Sheets("Distinct_List").Columns.AutoFit
End With
End Sub

Generally speaking - yes, this is what I'm hoping to get. The list in area B4:B in worksheet Jan to be compared to the list in the area B4:B in worksheet Feb and the unique values from these 2 lists to be populated in B4:B in Distinct worksheet.

Thanks AB33. It works Ok for Jan & Feb worksheets.

I would need to adjust it a little bit to work for specific worksheets indicated in A1 & C1 (there is a dropdown list which should allow users to change the parameters), keep the original format of the data (they all have to have 10 symbols) and the list to be sorted in ascending order.

Now, works in Jan and Feb, but if you wanted in all sheets, change this line

If ws.Name <> "Mar" And ws.Name <> "Distinct_List" Then

INTO

If ws.Name <> "Distinct_List" Then

I was actually thinking of something like this:

If ws.Name = ActiveSheet.Range("A1") And ws.Name = ActiveSheet.Range ("C1") Then

This should limit the the number of lists to compare to 2

Good idea, but when you run the code, the cursor has to be on the active sheet.

The cursor will certainly be there. The idea is that the macro should run only when the values in A1 and C1 change. Would you know how to condition that in the macro?

Hi AB33,

Do you have a suggestion how should I amend this code to have a similar list displayed horizontaly ruther than vertically?

Thanks in advance for any feedback.

Eduard

Change this line of the above code.

Sheets("Distinct_List").Range("B4").Resize(.Count).Value = WorksheetFunction.Transpose(.Keys)

INTO

Sheets("Distinct_List").Range("B4").Resize(.Count).Value =.Keys

Sorry, but it doesn't make any difference. The list is displayed exactly the same way. Any other ideas?

Sorry!
My bad

Sheets("Distinct_List").Range("B4").Resize(, .Count).Value = .Keys

Only now I have realized it doesn not work the same way. My fault.

Perfect.

Just out of curiosity, why do you use:
= WorksheetFunction.Transpose(.Keys)

if it works excatly the same whay with just
= .Keys