Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
"Harish Mohanbabu" wrote in message
... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
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". -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
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. -- Regards, Tom Ogilvy "Harish Mohanbabu" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
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 -----Original Message----- "Harish Mohanbabu" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
"Harish Mohanbabu" wrote in message
... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparting two excel sheets ...
Cheers Roger ...
It is working fine now. Many thanks for your help. Thanks once again, Harish Mohanbabu -----Original Message----- "Harish Mohanbabu" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I print sheets in Excel without blank sheets after page | New Users to Excel | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |