Table cross-check

[libre.]

Hello,

I have two huge tables that i need to compare and find out diferences beetween them.
Tables are randomly sorted so basicly i need full cross check.


Exemple:


Frst row from first table can be anywhere in second table. I only need to know is it there! If it's not, need to be marked or copied in third sheet..i dont really care.


Tables can be compared column G - DOC NUMBER.


So if anyone has an idea i would be grateful.

Win 7
Office 2013
http://i57.tinypic.com/adlv2w.jpg

[RudiS]

When you say it needs to be marked and copied into sheet 3, is it just the code, or do you want the entire record from sheet 2 that is not found in the sheet 1 to be copied into sheet 3?

[libre.]

I need any form of collecting differences. Best solution would be if i could seperate them in third sheet (hole rows copied in third sheet) but if you have other idea it's cool

[RudiS]

Here is a macro that will extract the records on sheet 2 to sheet 3 if it is not on sheet 1
It also marks the records on sheet 2 in blue is they are not on sheet 1. (Basically the record in blue are the ones that have been copied to sheet 3).

The macro assumes your sheets are called Sheet1, sheet2 and sheet3.

Try it out and see if it works for you.
Copy the entire macro and paste it into a module in the workbooks VBA editor.
Then run it.

Please Login or Register  to view this content.

[libre.]

Thank you for effort but it didnt work for me...
I think the best way is this...
I uploaded whole sheet to box.net so you can try yourself...maybe im doing something wrong...

here is the link of sheet

https://app.box.com/s/s3o865ezazsrmfg2aqot

Thanks!

[RudiS]

Hi Libre,

Here is a quick and dirty solution, but VERY accurate. (No macro this time...just formulas)

Follow these steps:
Insert a new column "A" in sheet 2
Type this formula in cell A2 : =TRIM(H2)
Fill the formula down to the bottom with autofill

Now go to sheet 1 and insert a new column "A" in this sheet
Next to the first record in cell A4, type this formula : =MATCH(TRIM(H4),Sheet2!$A:$A,0)
Fill the formula down to the bottom with autofill

Switch on autofilter on the first in sheet 1
Use the autofilter to filter for #N/A
These records with #N/A are NOT found in sheet 2.

[libre.]

Thanks again but match formula has an error..:-)
Maybe its best to download my sheet and try it yorself

And just reminder...i need crosscheck, showing differences:

1. From Sheet1 that doesnt exist in Sheet2
2. From Sheet2 that doesnt exist in Sheet1


Thanks again!

[ChemistB]

Upload the sheet to this forum (Go Advanced>Manage Attachments) Trim it down to a manageable size if it's very large.

[libre.]

Can i upload 5mb file?

[ChemistB]

No, try zipping it or just include enough data to show us what you are trying to do.

[libre.]

Here it is..

[ChemistB]

Okay, so none of the headers in sheet1 match headers in sheet2. Which columns in sheet1 need to be matched in which columns in sheet2? Did you say that the columns will be switching around so that sometimes it'll be column B in 1 compared to G in 2 and next time it will be C in 1 compared to D in 2? If we could just match headers, it would be a lot easier.

[RudiS]

Thanks again but match formula has an error..:-)
Maybe its best to download my sheet and try it yorself

And just reminder...i need crosscheck, showing differences:

Hi,

I did download your file from box.
The formulas I set up were on that large file and they worked fine, exactly to the steps I posted.
Not sure why is did not work for you.

Anyways...I do admit that I only did the check from one side...

[libre.]

Ok, the headers are not the same but i colored the header REFERENCE that can be used for crosscheck.

Maybe we will find solution

Thanks for help everyone..

[libre.]

To make things simple, can you mark each missing by color?
Sheet1 – color marked rows that doesn’t exist in Sheet2
Shee2 – color marked rows that doesn’t exist in Sheet1

I can easily filter results by color and take what I need, so no need for Sheet3 as result sheet?

Thanks.

[RudiS]

Hi,

See the attached sample workbook (sheet 3)
In the small list, my formula works well and I can confirm its accuracy.

If I apply this formula to your reference numbers, then I'm not sure if I can verify the results? They look suspicious?
Please confirm...