Comparting two excel sheets ...

[Harish Mohanbabu]

Hi,

I have got an Excel document which contains 2 different
sheets. These sheets contains customer details extracted
from 2 different systems.

The following fields are common between the sheets -

1. Customer ID
2. Name
3. Address details etc.

What I want to do is - Using the common 'Customer ID'
field, I want to compare the two sheets for checking data
integrity.

How can I do this? Do I have to do some sort of
programming or is there any other way ?

Please advise.

Thanks,

Harish M

 

[marty]

You can accomplish this task two different ways that I
know of. You can create a pivot table to create a report
of the two spreadsheets. You will then need to create a
report a report to display the integrity of the combined
data. Your question is to broad to explain how you would
do this.

Or, you can link (not import) the Excel worksheets into
Access and create a query to display the discrepancies
and/or matches. Because the worksheets are linked, any
changes to the Excel workbook will be realized real time
in Access.

It is much easier, by far, to do what you are asking, in
Access. Honestly, I don't mean to say it's impossible to
do in Excel but to me it's like trying to put a square peg
in a round hole. You can do it if the hammer's big enough
and you pound long enough.

Marty

 

[Roger]

The following fields are common between the sheets -
1. Customer ID
2. Name
3. Address details etc.

Suggest use the Vlookup function to compare data. See Excel help for syntax
details.
eg. If Sheet1 and Sheet2 contain CustomerID in column A, Name in column B
and Address in column C insert the following functions:
Cell D2 of Sheet1 =VLOOKUP(A2,Sheet2!A:C,2,FALSE)
Cell E2 of Sheet1 =VLOOKUP(A2,Sheet2!A:C,3,FALSE)
Then fill this formula down to the end of the data area on Sheet1.
Sheet 1 Column D will then show the matching Name from Sheet 2.
Sheet 1 Column E will then show the matching Address from Sheet 2.
If no data is found on Sheet2 for a CustomerID on Sheet1 then #N/A will be
returned for that row.
You can then easily compare Name and Address data by using a True/False
formula: B2=D2 and C2=E2 etc.
Modify the above example to reverse the lookup from Sheet2 to Sheet1 to show
details on Sheet2 which are not on Sheet1.

HTH

Roger

 

[Rick]

If you provide more detailed information, but in a simple
way with a small sample of data, I'm sure someone here
could help you.

With Visual Basic programming, just about anything seems
to be possible with the manipulation of data in MS Excel -
so that you often don't really need MS Access. (At least
that has been my experience). With VB or VBA, you can
create all sorts of things nobody has even thought about.
It just takes a lot of imagination and persistence. This
newsgroup is great to help a person work their way through
it, when they get stuck or "bottle-necked".

 

[Tom Ogilvy]

It depends on what you want to check, but look in Excel help for the vlookup
function.

This will allow you to show data from one sheet on another sheet so you can
make the comparison.

 

[Harish Mohanbabu]

Hi Roger,

Thanks for your post. I did as you suggested and it works
but not completely.

Both my sheets has got rows numbering to thousands. When I
ran the command from Sheet1, I got matching results for
only first 10 rows. After that, though the exact match is
there in the second sheet, I got "#N/A" message.

What could be wrong here? Please advise.

Thanks,

Harish

 

[Roger]

Hi Roger,

Thanks for your post. I did as you suggested and it works
but not completely.

Both my sheets has got rows numbering to thousands. When I
ran the command from Sheet1, I got matching results for
only first 10 rows. After that, though the exact match is
there in the second sheet, I got "#N/A" message.

What could be wrong here? Please advise.

Thanks,

Harish,

VLOOKUP requires an exact match when looking up data. You should be able to
see any obvious differences eg."." or "-" in the CustomerID data but spaces
are not visible. Suggest you use the TRIM function to clean up any
unneccessary spaces in the data - produce an extra column with the =TRIM
function then copy and paste values back to your Customer ID column.

The other problem can be mixing Text and Numeric data - Numbers can be
formatted in Excel as either Text or Numbers and so I suggest you
standardise your CustomerID field to only one of these formats. Then use
the Excel functions VALUE or TEXT as appropriate to change all your
CustomerID data to this format and copy/paste values as before.

If your CustomerID data looks identical, has no extra spaces and has the
same format in both sheets then VLOOKUP should match it.

VLOOKUP is a very useful function which will work with your large worksheets
but it can be very slow, depending on the speed of the PC.

Good luck,

Roger

 

[Harish Mohanbabu]

Cheers Roger ...

It is working fine now. Many thanks for your help.

Thanks once again,

Harish Mohanbabu