Check multiple column on 2 worksheets and report mismatch

[KumarG]

Hello all, am new to this forum and this is my first post.

I have been trying to work on a requirement to check multiple column on two different sheets of a workbook, and report only differences (if any) on a different sheet. I haven't been successful fo far, so any help or idea how to go about it would be very helpful.

I have tried to explain the requirement below.

Two sheets named "F12" and "P12", both are differerent in formats. There is a common unique reference in both sheets, i need to compare both sheets, look that unique reference, and compare about 8 different columns, and if the values match then ignore, but if not then report them in a different sheet, one below the other. Another point to mention is, the unique references could be repeated more than once, in which case it has to sum the value in the column before comparing it with the relevant column in the other sheet. Also the unique reference may be missing in one of the sheets (just available in one sheet), in that instance that will have to be reported as a difference as well.

[TABLE="class: cms_table, width: 263"]
<tbody>[TR]
[TD]Sheet Name[/TD]
[TD]F12[/TD]
[TD][/TD]
[TD]P12[/TD]
[/TR]
[TR]
[TD]Unique Ref[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CD[/TD]
[TD]=[/TD]
[TD]AJ[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CE[/TD]
[TD]=[/TD]
[TD]AM[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CF[/TD]
[TD]=[/TD]
[TD]AO[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]DF[/TD]
[TD]=[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]DH[/TD]
[TD]=[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CS[/TD]
[TD]=[/TD]
[TD]AR[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CT[/TD]
[TD]=[/TD]
[TD]AX[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]CU[/TD]
[TD]=[/TD]
[TD]AZ[/TD]
[/TR]
</tbody>[/TABLE]


I have created a sample file with some test/examples, but unable to attach it (example of data below).
[TABLE="width: 632"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Unique ref[/TD]
[TD]Sheet P12[/TD]
[TD]Sheet F12[/TD]
[TD]Status[/TD]
[TD]Copy to Result Sheet[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Same Value[/TD]
[TD]Same Value[/TD]
[TD]Match[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]Same Value[/TD]
[TD]Same Value (but in 2 rows of data)[/TD]
[TD]Match[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]Value[/TD]
[TD]NA (Missing)[/TD]
[TD]Mismatch[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]Different Value[/TD]
[TD]Different Value[/TD]
[TD]Mismatch[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]56789[/TD]
[TD]NA (Missing)[/TD]
[TD]Value[/TD]
[TD]Mismatch[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Once the 2 sheets are compared, i would like the mismatches to be reported/listed in "Result" sheet, from column K20 as per below.

[TABLE="width: 640"]
<colgroup><col style="width:48pt" span="2" width="64"> <col style="width:48pt" span="8" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]Unique ref1
[/TD]
[TD="class: xl66, width: 64"]F12
[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P12
[/TD]
[TD="class: xl64, align: right"]98.00
[/TD]
[TD="class: xl64, align: right"]50.00[/TD]
[TD="class: xl64, align: right"]25.00[/TD]
[TD="class: xl64, align: right"]75.00[/TD]
[TD="class: xl64, align: right"]200.00[/TD]
[TD="class: xl64, align: right"]99.00[/TD]
[TD="class: xl64, align: right"]55.00[/TD]
[TD="class: xl64, align: right"]88.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]Unique Ref2
[/TD]
[TD="class: xl66"]F12[/TD]
[TD="class: xl64, align: right"]250.00
[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl65, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P12[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00
[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl65, align: right"]150.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00[/TD]
[TD="class: xl64, align: right"]250.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]Unique Ref3
[/TD]
[TD="class: xl66"]F12[/TD]
[TD="class: xl64, align: right"]80.00[/TD]
[TD="class: xl64, align: right"]80.00
[/TD]
[TD="class: xl64, align: right"]80.00[/TD]
[TD="class: xl64, align: right"]80.00[/TD]
[TD="class: xl64, align: right"]80.00[/TD]
[TD="class: xl64, align: right"]80.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]P12[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
</tbody>[/TABLE]

I hope this is achievable, please let me know if the details are not clear or if you need further details. Any help/guidance would be really appreciated.

 

[hiker95]

KumarG,

Welcome to the MrExcel forum.


We would like more information. Please see the Forum Use Guidelines in the following link:

Guidelines for Forum Use


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results worksheet should look like.

Attachments


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

Dropbox

 

[KumarG]

Hello hiker95, thanks for your response, and for the useful links, they were helpful. I have now provided the link below for the sample file with test data, and the expected results in the "Result" sheet. I have added some additional details in the "Details" sheet. Any help would be greatly appreciated.

Dropbox - Testdata3.xlsb

 

[KumarG]

In case you can't access the link provided earlier, please find below the new link

Dropbox - Testdata3.xlsx

 

[hiker95]

Hello hiker95, thanks for your response, and for the useful links, they were helpful. I have now provided the link below for the sample file with test data, and the expected results in the "Result" sheet. I have added some additional details in the "Details" sheet. Any help would be greatly appreciated.

Dropbox - Testdata3.xlsb

KumarG,

Both files are opening up as Read Only?


Instead of the actual file(s), how about trying screenshots:


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results worksheet should look like.

Attachments

 

[KumarG]

Hello hiker95, the data on the files goes upto column DW, i.e, over 100 columns, so am not sure how to update the screenshot of the spreadsheet.

For info, link below for this post on ExcelGuru:
Multiple column check on 2 worksheets

 

[hiker95]

Hello hiker95, the data on the files goes upto column DW, i.e, over 100 columns, so am not sure how to update the screenshot of the spreadsheet.

For info, link below for this post on ExcelGuru:
Multiple column check on 2 worksheets

KumarG,

When I click on your latest link, the MrExcel site is returning the following warning:

Warning! The intended destination is not part of MrExcel Message Board.
If you do not recognise Multiple column check on 2 worksheets or do not trust the person who gave you this link, close this tab or click the Back button in your browser.

Sorry, but, I will not go to another site, per the above.

Maybe someone else on MrExcel will be able to help you.