How to Compare 2 Excel Worksheets

hi All how to I compare 2 Excel worksheets with a where clause

I have 2 excel worksheets with a data dump from some database (we do not have access to them).  I need to compare both of them but I would like to compare them where a row in the column is the same

for example, in both excel sheets there is a column named ID and these do match each other. all the rest of the columns can be different. and I need to compare those columns with each other

Thank you in advance
thomasm1948Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Are all the columns in each 'dump' (worksheet) in the same order?

Do both worksheets contain the same number of columns for each record?

Are the records a fixed column quantity, or do the number of columns varying on a record-by-record basis?

Also, what is the typical number of records in each worksheet ('dump')?
0
thomasm1948Author Commented:
From what I can see is that the worksheets may have an ID that the other one does not have.  The column names and the amount of columns in both worksheets are the same.

There is 672 records in one worksheet and 677 in the other.

The goal is to reconcile the rows where the id match and if there are ID that do not match then do a separate research on why they are there.

If there is a difference then we have to go into one of the systems and manual change the values
0
[ fanpages ]IT Services ConsultantCommented:
OK.  Thanks.

So, if using one worksheet (say, the one with 672 records) a matching "ID" is found in the second worksheet (with 677 records), you would then like to interrogate each & every column of the located record to establish if it is an exact match column-by-column, or not.

How many columns are to be checked between the two records (one in one worksheet, the other in the second worksheet)?

Also, how are exact matches to be highlighted, or how are matching "IDs" but incomplete (100%) matches of all other columns to be presented back to you?

Do you require a third (or subsequent) worksheet to show the results, or colo(u)r the cells of one/other/both worksheet in some way to indicate the outcome of the respective row?

Finally... if both recordsets originate from a database, can you not compare directly between the two tables/databases?
0
thomasm1948Author Commented:
There are only five columns only.  that includes the ID column.  For the most both work sheets have the same IDs.  the reason why has an ID that the other would not is if someone closed out an account and did not update it in both systems

So what I would need to find the matching ID and then see if the 4  columns to the right match

having a third worksheet would be helpful

The reason why I cannot compare the database tables is because we do not have access to them to run queries against them.  they are a hosted solution
0
[ fanpages ]IT Services ConsultantCommented:
If you are not interested in which of the final four columns are mismatched (although it would still be possible with a little more work) Is it practical to create a concatenated "key" column from all but the "ID" column & have this as an additional column (a sixth column) in both worksheets, so that once a matching "ID" is located, it will be a simple case of comparing the sixth column in one worksheet with the sixth column in the other worksheet.

If the values match; all columns are the same.  If the values of the sixth (concatenated) column do not match, then at least one of the columns is different (for this same "ID").
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.