Compare two data sets from two worksheets and output into 3 different sheets

[huyza_0100]

I know this has been asked a bazillion times on comparing two columns / sheets / workbooks but I can't seem to find out how to do this. My scenario is that I want List A to be compared with List B and have the rows copied to their respective sheets based on criteria of: missing, unique, match. I've found a snippet that does just that except it's on one sheet and doesn't copy the entire row. Any help would be greatly appreciated!

Please Login or Register  to view this content.

[davesexcel]

Check it out.

Please Login or Register  to view this content.

[huyza_0100]

Good morning, Dave!

Thanks for the quick reply and update! The code works perfectly but is there a way to split all the data into 3 arrays and output it just once without the loop?

[davesexcel]

I never worked much with arrays, even with arrays, you are still looping.

[huyza_0100]

Dang. I'll use your code and try to mess around with arrays more. I'm trying to understand them myself ^_^

[Fluff13]

How about

Please Login or Register  to view this content.

[huyza_0100]

Hi Fluff!!

Thanks for the code! It's much closer to what I was looking for! I'm getting a subscript out of range though. The debug line highlights this line of code:

Please Login or Register  to view this content.

Is there a solution for that?

[Fluff13]

Are you getting that on your test file, or on real data?
As it works for me on the test file.

[huyza_0100]

test file still. I've only changed the values of the numbers and added letters. The first time I ran it, no problems with the sample numbers but when I changed the sample data I received an error. Attached will be the same file with your code.

EDIT: Looks like I can't have any data in column B in Sheet2. Is it possible to include that in the data as well?

[Fluff13]

Ok how about

Please Login or Register  to view this content.

[huyza_0100]

that works! Thanks again Fluff!!! I'll have to study your array setup and see if I can break it down and learn it myself.

[Fluff13]

Glad to help & thanks for the feedback

[huyza_0100]

Hey Fluff,

Sorry. I'm still having issues...

Please Login or Register  to view this content.

I'm getting run time error '5' this time...Maybe there's a clash in the calling / naming?

EDIT: Seems like the issue is with the transpose: expression not defined in context. Anyone know what that means?

[jindon]

Try

Please Login or Register  to view this content.

[huyza_0100]

Jindon, that looks hella complicated but it works! No errors! Thanks for the amazing code!!

[jindon]

You are welcome and thanks for the rep.

If you want something like your original then

Please Login or Register  to view this content.

[huyza_0100]

the second snippet definitely is easier to read! Thanks again for the help!