compare for equal numbers and color code

Memar

Board Regular
Joined
Sep 2, 2011
Messages
76
I just want to compare two different reports total to check if the numbers in bold matches. For report A, the January 1 total number 800 is not correct comparing to report B. Because 200 item are not recorded so I wan to flag that with color or other best ways. The same with May 1 records. I want the color or flag to be in Report A in the total number Row or if the next row after the Total number.

Report A
1/1/2019 2/1/2019. 3/1/2019. 5/1/2019. Product identifier
[TABLE="width: 500"]
<tbody>[TR]
[TD]500[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]200[/TD]
[TD]1235[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]100[/TD]
[TD]350[/TD]
[TD]100[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]800[/TD]
[TD]200[/TD]
[TD]850[/TD]
[TD]300[/TD]
[TD]1900[/TD]
[/TR]
</tbody>[/TABLE]


Report B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date received[/TD]
[TD]store code [/TD]
[TD]amount [/TD]
[TD]Total Amount received by date and store [/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]12355[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]12355[/TD]
[TD]300[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]12355[/TD]
[TD]200[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]12344[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]12344[/TD]
[TD]100[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]
5/1/2019 12356 200. 500
5/1/2019. 12356 100. 500
5/1/2019. 12356. 200. 500

Thanks for your time and help.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You could use Conditional Formatting.

Using the example below.
Highlight cells A5 to D5 (use your actual total row).
Then goto
-Conditional Formatting
-New Rule
-Use Formula
-Enter formula shown in cell A5 (again change cell ranges in formula to match your actual data).
-Format
-Fill (choose color you want)
-OK
-OK
Excel Workbook
ABCDE
1Report A
21/1/20192/1/20193/1/20195/1/2019Product identifier
35001005002001235
43001003501001234
5800200850300
6
7
8Date receivedstore codeamountTotal Amount received by date and store
91/1/2019123555001000
101/1/2019123553001000
111/1/2019123552001000
122/1/201912344100200
132/1/201912344100200
145/1/201912356200500
155/1/201912356100500
165/1/201912356200500
Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A51. / Formula is =A5<>SUMIFS($C$9:$C$16,$A$9:$A$16,">="&A$2,$A$9:$A$16,"<="&EOMONTH(A$2,0))Abc
 
Upvote 0
Thank you for your quick response. It works perfectly.How can I modify this formula when the dates should be exact the same?

Thanks again.
 
Upvote 0
What do you mean by "when the dates should be exact the same"?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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