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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

I was actually thinking of something like this:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

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

Sorry!
My bad

Please Login or Register  to view this content.

Perfect.

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

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

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