"via135" wrote:
yes .. your method gives me the exact results i want!! thks!!

Glad to hear that ..

Extending the earlier set-up, this may suffice to provide the additional
summary presentations on the unreconciled amounts (whether DR or CR) that's
desired

Ref the sample construct available at:
http://www.savefile.com/files/4182256
Auto-reconciling DR n CR amounts in a col.xls

In sheet: X (earlier set-up, extended),

In H1:
=IF(ROW(A1)COUNT($E:$E),"",INDEX(E:E,MATCH(SMALL( $E:$E,ROW(A1)),$E:$E,0)))

In I1, copied to J1:
=IF(ROW(A1)COUNT($E:$E),"",INDEX(A:A,MATCH(SMALL( $E:$E,ROW(A1)),$E:$E,0)))

Select H1:J1, fill down to J100
(cover the same max extent that data is expected in cols A and B)

Cols I & J will return the unreconciled amounts (if any) and whether its a
DR or a CR, while col H returns the corresponding row numbers for these
(locations within the source data in cols A and B).

And as added visuals on the locations of the unreconciled amounts (say,
let's highlight unreconciled DR amts in red, CR amts in d.green) the source
data range within A1:B100 can be conditionally formatted using the formulas:

(Select A1:B100, then apply the CF settings below)

Condition 1
=AND($C1<0,$E1<"")
Format: Red fill & white font/bolded

Condition 2:
=AND($C10,$E1<"")
Format: Dark green fill & white font/bolded

If all is cool, ie there's no unreconciled amounts,
then there'll be no CF format triggered

.... and in sheet: Y,

re your query:
... BTW is there any way to match the same items
but in two columns,
say all DR items in COL A
and all CR items in COL B

Assume source data in row 2 up to row 30

In C2, copied down to C30:
=IF(COUNTBLANK(A2:B2)=2,"",SUM($B$2:B2)-SUM($A$2:A2))

Col C will return a cumulative "nett" reconciliation of the DR and CR
amounts in cols A and B (-ve figs = nett DR, +ve figs = nett CR)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---