Finding unmatched feilds in two identical tables (over 300K row)

dankar

Board Regular
Joined
Mar 23, 2016
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have two identical tables, I need to check any unmatched fields every week, what will be the best solution to check that, please help me in detail If possible.

Both tables have more than 100k records

* I need to find any unmatched fields so I can update it and keep both identical.

fields can have numbers and characters.

Table1
from Column A to Column G and Table2 from Column I to Column O ( both tables are identical having the same data)

Please let me know if you have any question?

Thanks

ABCDEFG
IDRoomFreezerShelfRackBoxPosition
1
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try using the Power Query: Make a query from each table and try either merging them column by column (using Left Anti join you're left with rows only in the first query etc.) or - if they have the same column names - you can append them and then group by all columns. Use the "Group by" on all columns & have two aggregations: First Count Rows and the second "All Rows". Next filter out everything but the ones where the Count is 1, remove all the columns but the one with the All Rows & expand that. You're left with only the unique rows in both. If the column names don't match, add another step in the original queries where you rename the column names in the other one so that they do. Also, you might want to add a step in both queries where you add a column with the table name in it so that it's easier for you to find out the which query the data row can be found from. And if you add an index column to both queries it'll be even easier for you to find the right row.

When ever you're adding new columns to your queries or you don't want to compare some of the existing ones just leave those out from the part where you're matching this column with that one - or in the case of Appending queries just leave them out of the Group By -step.

The beauty of using the Power Query is you only need to do the steps once. The next time it's enough for you to just press the "Refresh All" button found on the Data tab.
 
Upvote 0
Hello Dankar

I have some code that I think will work, but I need to know what to do if two rows do NOT match. Perhaps display a message someplace, and if so, where? Perhaps some other column or another sheet?

TotallyConfused
 
Upvote 0
Hello Dankar

I have some code that I think will work, but I need to know what to do if two rows do NOT match. Perhaps display a message someplace, and if so, where? Perhaps some other column or another sheet?

TotallyConfused
Hi.
I didn't get what you meant by two rows do not match. Can you give me an example.

Each row in table1 should match the one in table2 all feilds should be the same if one feild does not match I need to know which one.

I tried a way but it was very slow with this amount of rows.

I appended all the feilds for table one in the last column and did the same with table2 and then I used the formula (=if H1=P1, "T","F") It worked as i filtered then to the false results..but it took loong time which is not convenient.

Perhaps if you can use a code that any mismatched in any feild to extract the "ID" only and put it in another sheet. This would be fine

If there is a way to extract the whole row and put it in another sheet would be perfect.

I just want a fast way to know which "ID" have the difference so I can check it.

Any feild can be null or have mismatch entry EXCEPT for the feild "ID" is unique and can not be null and it matches the other ID in the second table

Hope I clarified it well.

Thanks again for the help
 
Upvote 0
Try using the Power Query: Make a query from each table and try either merging them column by column (using Left Anti join you're left with rows only in the first query etc.) or - if they have the same column names - you can append them and then group by all columns. Use the "Group by" on all columns & have two aggregations: First Count Rows and the second "All Rows". Next filter out everything but the ones where the Count is 1, remove all the columns but the one with the All Rows & expand that. You're left with only the unique rows in both. If the column names don't match, add another step in the original queries where you rename the column names in the other one so that they do. Also, you might want to add a step in both queries where you add a column with the table name in it so that it's easier for you to find out the which query the data row can be found from. And if you add an index column to both queries it'll be even easier for you to find the right row.

When ever you're adding new columns to your queries or you don't want to compare some of the existing ones just leave those out from the part where you're matching this column with that one - or in the case of Appending queries just leave them out of the Group By -step.

The beauty of using the Power Query is you only need to do the steps once. The next time it's enough for you to just press the "Refresh All" button found on the Data tab.
Thanks Misca , I don't have in idea about the power query I will try to check.
I thought if there is VB code I can use to get the results.thanks again
 
Upvote 0
Hello Dankar
Thanks for answering my question so quickly. I think the VBA code I have should work for you. I just need to make a few adjustments to it so it will fit your situation. What I have should be a lot faster than the method you tried. I'm sorry for the confusion I caused by referring to 'Two rows that do not match'. Actually, that should have been where a field from one table does not match the field from the other table.

TotallyConfused
 
Upvote 0
Hello Dankar
Thanks for answering my question so quickly. I think the VBA code I have should work for you. I just need to make a few adjustments to it so it will fit your situation. What I have should be a lot faster than the method you tried. I'm sorry for the confusion I caused by referring to 'Two rows that do not match'. Actually, that should have been where a field from one table does not match the field from the other table.

TotallyConfused
Thank you so much,please let me know when its ready thanks again
 
Upvote 0
Hello Dankar

First of all I want to sincerely apologize for taking so long on this project. There's been a family illness that I've had to deal with. Hopefully all will be well now. You are a VERY patient person and I THANK YOU for that.

1 ) I need to know the name of that worksheet where you have your Table 1 and Table 2 data.
2 ) The program I have written now will create and store any errors on a new sheet named 'Data Errors'. If you already have such a sheet, or if you'd like it to be called something else, what would you like the name to be?

TotallyConfused
 
Upvote 0
Hello Dankar

First of all I want to sincerely apologize for taking so long on this project. There's been a family illness that I've had to deal with. Hopefully all will be well now. You are a VERY patient person and I THANK YOU for that.

1 ) I need to know the name of that worksheet where you have your Table 1 and Table 2 data.
2 ) The program I have written now will create and store any errors on a new sheet named 'Data Errors'. If you already have such a sheet, or if you'd like it to be called something else, what would you like the name to be?

TotallyConfused
Thank you TotallyConfused, sorry to hear that. hope you are all ell now.

for :
1: Table1 will be from cell A1-G1 and table2 will be from cell H1-N1
2: Data Errors is perfect.

Thank you again for your help.
 
Upvote 0
Hello Dankar

Just a couple of quick questions.

1 ) I need to know the name of the worksheet that contains your Table 1 and your Table 2. This information is needed so I can make that worksheet the active one, as my program processes your data.

2 ) In your original post, you said that:
Table1 from Column A to Column G and Table2 from Column I to Column O
In your last message you said:
Table1 will be from cell A1-G1 and table2 will be from cell H1-N1

I see that Table1 is still at the same location, HOWEVER, the location of Table2 is now different. Has your data changed? If it has, I can adjust the program. I do need to know the exact location of table2.

TotallyConfused
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top