Req formula to compare breakup of cells with consolidated amount

[Nil70]

Hello Friends,

I am new here, require help to compare the values of breakup cells vs consolidated cells. Example as below.
I need to compare D1(employee code) which is having some consolidated total amount in E1. Now I have breakup of that in A1 which is picked up from another working sheet (same employee code as D1) with breakup figures in B1 to B3. Same with D2 & E2. Now there is a figure mismatch between employee code D2 having values in E2 against sum of B4+B5+B6. I need a formula where there is a mismatch in sum against particular employee code, that employee code should highlighted with some colour, so I can compare those two columns & do necessary rectification. This is nothing but comparison between columns having breakup & consolidated amount.


<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.5in; mso-footer-margin:.5in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:204; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 355"]
<tbody>[TR]
[TD="width: 71, align: center"]A[/TD]
[TD="width: 71, align: center"]B[/TD]
[TD="width: 71, align: center"]C[/TD]
[TD="width: 71, align: center"]D[/TD]
[TD="width: 71, align: center"]E[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]5840[/TD]
[TD][/TD]
[TD="align: right"]1234567[/TD]
[TD="align: right"]92572[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]85985[/TD]
[TD][/TD]
[TD="align: right"]2123456[/TD]
[TD="align: right"]21300[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]747[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2123456[/TD]
[TD="align: right"]4568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2123456[/TD]
[TD="align: right"]7893[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2123456[/TD]
[TD="align: right"]8894[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[jtakw]

Hi,

You can use my formula in G1 as your CF rule formula for Column D and/or E, this is based on the setup in your/this sample, if your actual setup is different, the formula will need to be adjusted:

Excel 2010
	A	B	C	D	E	F	G
1	1234567	5840		1234567	92572		FALSE
2	1234567	85985		2123456	21300		TRUE
3	1234567	747
4	2123456	4568
5	2123456	7893
6	2123456	8894
Sheet19

Cell Formulas
Range	Formula
G1	=SUMIF($A$1:$A$6,$D1,$B$1:$B$6)<>$E1

 

[Nil70]

Thank you very much jtakw, it is working.
The only problem is in my sheet column A has more than 3000 entries, against I have to compare aprx 900 entries of column D. Too much manual work for every different number of column A.

Thanks once again for your help.

 

[jtakw]

Thank you very much jtakw, it is working.
The only problem is in my sheet column A has more than 3000 entries, against I have to compare aprx 900 entries of column D. Too much manual work for every different number of column A.

Thanks once again for your help.

If you have the list of unique IDs in Column D (approx. 900), you use the same formula for the entire range of column D, no need to change the Cell reference for each ID, the formula will self adjust.

For this sample, select d1 and drag down to end of your data in Column D, click Conditional Formatting, use formula to...., enter the formula, click format, format as you like.

 

[markmzz]

Thank you very much jtakw, it is working.
The only problem is in my sheet column A has more than 3000 entries, against I have to compare aprx 900 entries of column D. Too much manual work for every different number of column A.

Thanks once again for your help.

Hi!

Try this in Conditional Formatting:

Columns A and B

=SUM(($A$1:$A$6=$A1)*$B$1:$B$6)=VLOOKUP($A1,$D$1:$E$2,2,0)<strike></strike>

Columns D and E

=SUM(($A$1:$A$6=$D1)*$B$1:$B$6)=$E1


[TABLE="class: grid, width: 461"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]5840[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]92572[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]85985[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD="align: center"]2123456[/TD]
[TD="align: center"]21300[/TD]
[TD="align: center"]FALSO[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1234567[/TD]
[TD="align: center"]747[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2123456[/TD]
[TD="align: center"]4568[/TD]
[TD="align: center"]FALSO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2123456[/TD]
[TD="align: center"]7893[/TD]
[TD="align: center"]FALSO[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FALSO[/TD]
[TD="align: center"]VERDADEIRO[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]2123456[/TD]
[TD="align: center"]8894[/TD]
[TD="align: center"]FALSO[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]********[/TD]
[TD]*******[/TD]
[TD]************[/TD]
[TD]********[/TD]
[TD]*******[/TD]
[TD]************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz