listing unmatched items!

[via135]

hi!

i am having the following data in A1:B22

1000 DR
1000 CR
1500 DR
1000 DR
2000 DR
1000 DR
1500 CR
2000 CR
1000 DR
1000 CR
1000 DR
2000 CR
2000 DR
1500 CR
1000 CR
3000 DR
1000 CR
4000 DR
3000 CR
2000 DR
2000 CR
1500 DR

here in the list

1000 DR - 5 rec
1000 CR - 4 rec

1500 DR - 2 rec
1500 CR - 2 rec

2000 DR - 2 rec
2000 CR - 3 rec

3000 DR - 2 rec
3000 CR - 1 rec

4000 DR - 1 rec
4000 CR - nil

what i want is to shortlist the records

1000 DR
2000 CR
3000 DR
4000 DR

using worksheet function in COL C
to mark some flag like
"matched", "unmatched"

hopes that explained the things better!!

-via135

 

[Max]

Here's one formulas play which will offset/cancel the DR figures against
similar CR figures in cols A and B, and list the outstanding results, if any
remain after the cancellations, in a col F (this is what I gather you're
really trying to do here)

Assuming source data within cols A and B,
within row1 to say, a max expected row100

In C1:
=IF(B1="","",IF(B1="DR",-A1,A1))

In D1:
=IF(C1="","",COUNTIF($C$1:C1,C1))

In E1, array-entered (press CTRL+SHIFT+ENTER):
=IF(D1="","",IF(ISNUMBER(MATCH(-C1&"_"&D1,$C$1:$C$100&"_"&$D$1:$D$100,0)),"",ROW()))

In F1:
=IF(ROW(A1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

Select C1:F1, copy down to F100

Col F will list the figures from col C which have no match, if any (-ve figs
simply means DR, +ve figs means CR). Results will be neatly bunched at the
top.

Col C's formulas is simply to change the DR figures in col A to -ve, while
CR figures will remain as they are.

Based on your sample data within A1:B22, we'd get in listed in col F:
-1000
-4000

Adapt the range in E1's array formula
to suit the actual extent of your data before copying down

 

[Max]

Just to clarify that for the sample data posted in A1:B22,
believe your "summary" contains 2 errors

Lines:
should have read as:

2000 DR - 3 rec
2000 CR - 3 rec

3000 DR - 1 rec
3000 CR - 1 rec

Think the outstanding amounts, after cancellations,
returned by the formulas in col F are correct, viz.: -1000, -4000

 

[via135]

hi!

yes..your method gives me the exact results i want!!
thks!!

i've noticed the error only after reading your reply..!!
sorry..!! should be due to naked eye counting!!

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

-via135

 

[Max]

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($C1>0,$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)

 

[via135]

thks Max

for the wonderful stuff..
lot more than what i expected..!!

thks a lot!!

-via135

 

[Max]

You're welcome, via135!
Happy reconciling ! <g>

 

[via135]

ha..ha..!!

ofcourse.. Max!!
will be remembering you
on every successful reconciliation!!

-via135