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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"via135" wrote:
>
> 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
>
>
> --
> via135
> ------------------------------------------------------------------------
> via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=561822
>
>

[Max]

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

Lines:
> > 2000 DR - 2 rec
> > 2000 CR - 3 rec
> >
> > 3000 DR - 2 rec
> > 3000 CR - 1 rec

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[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 ***** 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

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

Lines:
> > 2000 DR - 2 rec
> > 2000 CR - 3 rec
> >
> > 3000 DR - 2 rec
> > 3000 CR - 1 rec

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

"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($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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[via135]

thks Max

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

thks a lot!!

-via135

"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($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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

You're welcome, via135!
Happy reconciling ! <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"via135" wrote:
> thks Max
> for the wonderful stuff..
> lot more than what i expected..!!
> thks a lot!!

[[email protected]]

ha..ha..!!

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

-via135



Max wrote:
> You're welcome, via135!
> Happy reconciling ! <g>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "via135" wrote:
> > thks Max
> > for the wonderful stuff..
> > lot more than what i expected..!!
> > thks a lot!!