Compare 2 sets of data and highlight the difference value

A

Abinav

New Member
Joined
Sep 21, 2017
Messages
36
Hello

Please find my requirements. I have 2 sets of data which needs to be sorted to ascending order. Headers starts from Row 4. 1st data is with headers 'Account(with numbers- Col A and 'Total-Col B (with positive & negative values also ). 2nd data is with the same headers and values-Col D & E. I want to compare the column A & D first vice versa and i want the code to display the missing values in both the columns. then i want the code to compare the total columns B & E and display the amount does not match. Also I need the code to convert the values to number since the Account Column values are in text.
okdmr.jpg


Grand total fields needs to be ignored.
Note- Col B has few values in negative values which is there in Col E. Same value as below . code should ignore these values regardless of the negative figures since the values are same. For example,
Col B Col E
53024 -132 53024 132
53025 -7327.44 53025 7327.44

Thanks for your help :)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
First question: MATCH() or V.LOOKUP()

Second question:
[TABLE="width: 308"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]{=SUMPRODUCT(IF(A5=$D$5:$D$7,IF(B5<>$E$5:$E$7,1,0),0))}[/TD]
[/TR]
[TR]
[TD]Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hello

Sorry to be little late.

I have the pivot contents of 2 sets of data in PIVOT OUTPUT (sheet name). I want to use MATCH function in vba to compare both the PIVOT tables and display/highlight the differences in both the pivot values. I want to add the image of the PIVOT table sheet for reference. please help.



Thank you
 
Upvote 0
Hi

Currently i got this code to compare Col A and Col B. Please can you alter the code and tell me to compare Col A & Col D

Private Sub CommandButton1_Click()


Dim Report As Worksheet
Dim i As Integer, j As Integer
Dim lastRow As Integer


Set Report = Excel.Worksheets("Execute")


lastRow = Report.UsedRange.Rows.Count


Application.ScreenUpdating = False


For i = 2 To lastRow
For j = 2 To lastRow
If Report.Cells(i, 1).Value <> "" Then
If InStr(1, Report.Cells(j, 4).Value, Report.Cells(i, 1).Value, vbTextCompare) > 0 Then

Report.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
Report.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
Exit For
Else
Report.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
Report.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
End If
End If
Next j
Next i



For i = 2 To lastRow
For j = 2 To lastRow
If Report.Cells(i, 4).Value <> "" Then
If InStr(1, Report.Cells(j, 1).Value, Report.Cells(i, 4).Value, vbTextCompare) > 0 Then
Report.Cells(i, 2).Interior.Color = RGB(255, 255, 255) 'White background
Report.Cells(i, 2).Font.Color = RGB(0, 0, 0) 'Black font color
Exit For
Else
Report.Cells(i, 2).Interior.Color = RGB(156, 0, 6) 'Dark red background
Report.Cells(i, 2).Font.Color = RGB(255, 199, 206) 'Light red font color
End If
End If
Next j
Next i


Application.ScreenUpdating = True


End Sub






Thank you
 
Upvote 0
Code: 
For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 1).Value <> "" And Report.Cells(j, 4).Value <> "" Then
            If InStr(1, Report.Cells(i, 1).Value, Report.Cells(j, 4).Value, vbTextCompare) > 0 Then
                Report.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
                Report.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                If Report.Cells(i, 2).Value = Report.Cells(j, 5).Value Then
                    Report.Cells(i, 2).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 2).Font.Color = RGB(0, 0, 0) 'Black font color
                Else
                    Report.Cells(i, 2).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 2).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
                j = lastRow
            Else
                Report.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
         End If
    Next j
Next i
 
Upvote 0
Thanks Mart. It is comparing Columns A & D and highlighting the missing figures now. But i also need the code to compare Col D & A and display the highlight/differences in the Col D. Can you help?
 
Upvote 0
Code: 
For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 4).Value <> "" And Report.Cells(j, 1).Value <> "" Then
            If InStr(1, Report.Cells(i, 4).Value, Report.Cells(j, 1).Value, vbTextCompare) > 0 Then
                Report.Cells(i, 4).Interior.Color = RGB(255, 255, 255) 'White background
                Report.Cells(i, 4).Font.Color = RGB(0, 0, 0) 'Black font color
                If Report.Cells(i, 5).Value = Report.Cells(j, 2).Value Then
                    Report.Cells(i, 5).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 5).Font.Color = RGB(0, 0, 0) 'Black font color
                Else
                    Report.Cells(i, 5).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 5).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
                j = lastRow
            Else
                Report.Cells(i, 4).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report.Cells(i, 4).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
         End If
    Next j
Next i
 
Upvote 0
Thanks Mart. It is working well.
But it is also highlighting the columns B & E like below. Also the total value. I only want Col A & D values to be highlighted excluding the grand total.
[TABLE="width: 696"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Amount in doc. Curr.[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Sum of Amount in doc. Curr.[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Account[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53418[/TD]
[TD="align: right"]18568.92[/TD]
[TD][/TD]
[TD]53418[/TD]
[TD="align: right"]18568.92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30007[/TD]
[TD="align: right"]2889.22[/TD]
[TD][/TD]
[TD]30007[/TD]
[TD="align: right"]2889.22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56122[/TD]
[TD="align: right"]2310[/TD]
[TD][/TD]
[TD]56122[/TD]
[TD="align: right"]2310[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55811[/TD]
[TD="align: right"]2200[/TD]
[TD][/TD]
[TD]55811[/TD]
[TD="align: right"]2200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]97957270[/TD]
[TD="align: right"]1617[/TD]
[TD][/TD]
[TD]97957270[/TD]
[TD="align: right"]1617[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30080[/TD]
[TD="align: right"]703.24[/TD]
[TD][/TD]
[TD]30080[/TD]
[TD="align: right"]703.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]97273574[/TD]
[TD="align: right"]229.24[/TD]
[TD][/TD]
[TD]97273574[/TD]
[TD="align: right"]229.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]97957093[/TD]
[TD="align: right"]98.18[/TD]
[TD][/TD]
[TD]97957093[/TD]
[TD="align: right"]98.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]52439[/TD]
[TD="align: right"]-1.87[/TD]
[TD][/TD]
[TD]53017[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53018[/TD]
[TD="align: right"]-48.53[/TD]
[TD][/TD]
[TD]52439[/TD]
[TD="align: right"]-1.87[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5436[/TD]
[TD="align: right"]-94.59[/TD]
[TD][/TD]
[TD]97205614[/TD]
[TD="align: right"]-4.95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53024[/TD]
[TD="align: right"]-132[/TD]
[TD][/TD]
[TD]53018[/TD]
[TD="align: right"]-48.53[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53020[/TD]
[TD="align: right"]-2504.55[/TD]
[TD][/TD]
[TD]5436[/TD]
[TD="align: right"]-94.59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53023[/TD]
[TD="align: right"]-3407.77[/TD]
[TD][/TD]
[TD]53023[/TD]
[TD="align: right"]-3407.77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53021[/TD]
[TD="align: right"]-4884.9[/TD]
[TD][/TD]
[TD]53021[/TD]
[TD="align: right"]-4884.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53025[/TD]
[TD="align: right"]-7327.44[/TD]
[TD][/TD]
[TD]53025[/TD]
[TD="align: right"]-7327.44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]10214.15[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]12845.75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Mart

Can you tell me the code to remove minus sign from the Col E in the below list

[TABLE="width: 392"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vendor[/TD]
[TD]Customer[/TD]
[TD]LC pmt amt[/TD]
[TD]crcy[/TD]
[TD]Amount Paid[/TD]
[TD]Payment[/TD]
[/TR]
[TR]
[TD="align: right"]2183[/TD]
[TD][/TD]
[TD]54.49-[/TD]
[TD]AUD[/TD]
[TD]54.49-[/TD]
[TD]F110000001[/TD]
[/TR]
[TR]
[TD="align: right"]4222[/TD]
[TD][/TD]
[TD]55.80-[/TD]
[TD]AUD[/TD]
[TD]55.80-[/TD]
[TD]F110000002[/TD]
[/TR]
[TR]
[TD="align: right"]6537[/TD]
[TD][/TD]
[TD]203.64-[/TD]
[TD]AUD[/TD]
[TD]203.64-[/TD]
[TD]F110000003[/TD]
[/TR]
[TR]
[TD="align: right"]7741[/TD]
[TD][/TD]
[TD="align: right"]727.07[/TD]
[TD]AUD[/TD]
[TD="align: right"]727.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9019[/TD]
[TD][/TD]
[TD]1,665.97-[/TD]
[TD]AUD[/TD]
[TD]1,665.97-[/TD]
[TD]F110000004[/TD]
[/TR]
[TR]
[TD="align: right"]10670[/TD]
[TD][/TD]
[TD]273.54-[/TD]
[TD]AUD[/TD]
[TD]273.54-[/TD]
[TD]F110000005[/TD]
[/TR]
[TR]
[TD="align: right"]30080[/TD]
[TD][/TD]
[TD="align: right"]703.24[/TD]
[TD]AUD[/TD]
[TD="align: right"]703.24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50046[/TD]
[TD][/TD]
[TD]4,317.50-[/TD]
[TD]AUD[/TD]
[TD]4,317.50-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50878[/TD]
[TD][/TD]
[TD]597.97-[/TD]
[TD]AUD[/TD]
[TD]597.97-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]51399[/TD]
[TD][/TD]
[TD]4,028.85-[/TD]
[TD]AUD[/TD]
[TD]4,028.85-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]53017[/TD]
[TD][/TD]
[TD]480.00-[/TD]
[TD]AUD[/TD]
[TD]480.00-[/TD]
[TD]




[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
#1 then i want the code to compare the total columns B & E

Confused!!!

remove minus sign: ABS()

 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
310
kvsrinivasamurthy
K
N
  • Solved
Help with formula please, if two values match calculate the difference between 2 dates
2
Replies
11
Views
421
Newbie73
N
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
222
Madam_Xavier
M
J
  • Solved
Calculate sums based on values
Replies
3
Views
261
NateSC
NateSC
P
  • Question Question
Display unmatched values while comparing three different sheets.
Replies
1
Views
173
RobP
R

Forum statistics

Threads
1,223,719
Messages
6,174,089
Members
452,542
Latest member
Bricklin

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