Check multiple column on 2 worksheets and report mismatch

K

KumarG

New Member
Joined
Jul 24, 2017
Messages
4
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
KumarG said:
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
Click to expand...

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
 
Last edited:
Upvote 0
KumarG said:
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
Click to expand...

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.
Click to expand...

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

Maybe someone else on MrExcel will be able to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
369
ChrisMac1
ChrisMac1
M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
411
Alex Blakenburg
A
Guybrush Threepwood
  • Question Question
Using Filter for Criteria in Multiple Columns and a Specific Row Range?
Replies
0
Views
409
Guybrush Threepwood
Guybrush Threepwood
N
  • Question Question
Using formula to look up data where the data tab has the required data listed multiple times
Replies
1
Views
497
mumps
mumps
V
  • Solved
Deleting the same column nr's across multiple sheets based on dynamic cell values
Replies
4
Views
549
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top