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.

Sheet Name	F12		P12
Unique Ref	A		Q
Column	CD	=	AJ
Column	CE	=	AM
Column	CF	=	AO
Column	DF	=	Y
Column	DH	=	Z
Column	CS	=	AR
Column	CT	=	AX
Column	CU	=	AZ

<tbody>
</tbody>

I have created a sample file with some test/examples, but unable to attach it (example of data below).

Unique ref	Sheet P12	Sheet F12	Status	Copy to Result Sheet
12345	Same Value	Same Value	Match	No
23456	Same Value	Same Value (but in 2 rows of data)	Match	No
34567	Value	NA (Missing)	Mismatch	Yes
45678	Different Value	Different Value	Mismatch	Yes
56789	NA (Missing)	Value	Mismatch	Yes

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Unique ref1	F12
	P12	98.00	50.00	25.00	75.00	200.00	99.00	55.00	88.00
Unique Ref2	F12	250.00	250.00	250.00	250.00	250.00	250.00	250.00	250.00
	P12	250.00	250.00	250.00	150.00	250.00	250.00	250.00	250.00
Unique Ref3	F12	80.00	80.00	80.00	80.00	80.00	80.00
	P12

<colgroup><col style="width:48pt" span="2" width="64"> <col style="width:48pt" span="8" width="64"> </colgroup><tbody>
</tbody>

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.