multiple look ups

[Big Ben]

A B C D E F G
H
1 PO TRANS $ $ AMT $ AMT QTY QTY
# TYPE AMT RECV VCHR RECV VCHR
2 4227 RECV 668 668 0 26 0
3 4227 RECV 2,415.80 2,415.80 0 94 0
4 4227 VCHR 722 722 0 26
5 TOTAL 3,084 722 120 26 2362.34

For column F and G, I need each QTY RECV to have a matching QTY VCH
(***See note at bottom) whereby ultimately, the total RECV QTY equal
the total VCHR QTY within each ID set. Column F is the Qty RECV’d, an
Column G is the QTY VCHR’d. It is evident that Row 2 matches up wit
Row 4 @ 26 units, causing row 3 to put this ID set out of balance by 9
units. I have made an almost accurate assumption that if the Total ro
(Row 5) adds up, everything is OK with that set of ID’s and no furthe
evaluation needs to be done. But if it does not, such as in th
example, I would like the formula to enter the $ amount that is causin
the ID Set to be out of balance for ex, enter C3 in H5.

This is my current =IF(AND(ISBLANK(C5),J5-K5>0),H5-I5," ") statement.
However, notice how the result in H5 of $2362.34 does not quite matc
with the total I need, which is $2415.80. This current if statement i
taking the grand total for the entire ID set, whereas, I want the tota
for just the balance that is throwing it off.

1 more thing, it must be noted that each ID set may not contain th
same # of transaction (Column A) for ex, the next ID set #4228, ma
have 10 transactions.

NOTE
Also, it may be possible that 2 RCVD’s may total 1 VCHR.

I know this is a fairly lengthy explanation, but any ideas???

Thanks,

Be

 

[Frank Kabel]

Hi
one idea using a helper column H which taggs all RECV entries above the
sum of VCHR (works only for on PO TYPE). Enter the following in H2
=IF(C2="RECV",IF(SUMIF($C$2:$C$100,"VCHR",$G$2:$G$100)-SUMIF($C$2:$C2,"
RECV",$F$2:$F2)<0,"Above/Tagged",""),"")
and copy down