Table Matching and Comparing

[jlara0687]

1.PNG2.PNG3.PNG

I've Attached the file from the pictures above. Any assistance would be greatly appreciated. Thank You in Advance

[sandy666]

If you are able to use PowerQuery (free add-in for Ex2013 Pro Plus from MS site) try this
(PowerQuery is not a formula or vba so you will not see how it works until you install this add-in, now you can only see the result)

[jlara0687]

Thank You for your help,

I was was just recently able to get PowerQuery for my workstation. I've attached a similar document from the original post but with more specific instructions relating to PowerQuery. The below databases come from two different systems that should be talking to each other, but do not always match. What I am attempting to do is to look for mismatches by setting Database 1 and Database 2 side by side, matching Sector 1 from both databases, then comparing the mismatches between sector 2, 3, 4, and 5... This is only a representation of what the tables look like in a small scale.


This second example was created manually. Would like to learn how to use Excel PowerQuerty to create Database 1 and Database 2 as shown from Database 1 and Database 2 from above example. The Match / No Match is just for my own benefit to see where the missmatches are to be corrected and will be created manually.


I'd like to learn how to make it so that when comparing Database 1 and Database 2, matching by Sector 1; I could have in either Database 1 or 2 a row created as a blank depicting either a repeat occurred or that there is no such entry on opposite database. The example below has a blank row on Database 2 for row # 1. Entry in Database 1 for row 1 does not exist in Database 2, therefore a blank was created for Database 2. The same goes for rows 15, 16, and 17; Database 1 and Database 2. A blank row was created for row 16 and 17 on Database 2 since data on row 16 and 17 for Database 1 was repeated and did not exist in Database 2. Any help would be greatly appreciated

[sandy666]

Too much to read
You want to compare two tables?
One way is:

[jlara0687]

Your help is much appreciated. Basically what i am trying to do is match both tables by using a specific column. If in either table the data does not match for that column, a blank row will be created in the opposite table.

[sandy666]

1. table 1 ==> table 2
2. table 1 <== table 2
3. table 1 <==> table 2

which option you want?

edit:
third option you've in post #4

if you want compare by columns - merge these tables then use conditional columns 5 times (with result TRUE/FALSE or MATCH/NOT MATCH or whatever you want)

[jlara0687]

Option 4, the only problem is that I can do it manually buti usually deal with 1000+ rows, it would take hours and hours.

[sandy666]

Why rows? you need to compare columns - if A₁ = A₂ show TRUE else FALSE
exactly as in your example