flagging unmatched items!!!

[via135 via OfficeKB.com]

i am having the following data in A1:B22

COL A COL B

1000.00 DR
1000.00 CR
1000.00 DR
1000.00 DR
1000.00 DR
1000.00 DR
1000.00 CR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 DR
1000.00 CR
1000.00 DR

what i want is to flag off the 2 items of
1000 in COL A which are unmatched with ref to COL B,
ie)

1000.00 DR
1000.00 DR
in COL C

using appropriate formulae in C1: C22

hlp pl?

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

[Toppers]

In your example, with all values being the same, we don't know which pair
don't match. Using COUNTIF for CR & DR we can identify that there are 2 DRs
"not matched" (counts of 10 and 12 respectively).

Is this sufficient? Or do you want to compare to find balancing CR/DR values?

"via135 via OfficeKB.com" wrote:

> i am having the following data in A1:B22
>
> COL A COL B
>
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
> 1000.00 DR
> 1000.00 DR
> 1000.00 DR
> 1000.00 CR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
> 1000.00 CR
> 1000.00 DR
>
> what i want is to flag off the 2 items of
> 1000 in COL A which are unmatched with ref to COL B,
> ie)
>
> 1000.00 DR
> 1000.00 DR
> in COL C
>
> using appropriate formulae in C1: C22
>
> hlp pl?
>
> -via135
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200607/1
>

[via135 via OfficeKB.com]

yes.. i want the correct "countif" formula to list out the
overall unmatched (dr/cr) items list
by flaging in col C...??

-via135

Toppers wrote:
>In your example, with all values being the same, we don't know which pair
>don't match. Using COUNTIF for CR & DR we can identify that there are 2 DRs
>"not matched" (counts of 10 and 12 respectively).
>
>Is this sufficient? Or do you want to compare to find balancing CR/DR values?
>
>> i am having the following data in A1:B22
>>
>[quoted text clipped - 36 lines]
>>
>> -via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200607/1

[Toppers]

=Countif(B1:B22,"CR") and =Countif(B1:B22,"DR") will give respective overall
counts which are 10 and 12 so (as I said in my previous post) indicates 2
"extraneeous" DRs. In this case it suggests either a DR should be a CR to get
11/11 balance or 2 CRs are missing: there is no way (that I can think of) of
determining which situation is correct.

"via135 via OfficeKB.com" wrote:

> yes.. i want the correct "countif" formula to list out the
> overall unmatched (dr/cr) items list
> by flaging in col C...??
>
> -via135
>
> Toppers wrote:
> >In your example, with all values being the same, we don't know which pair
> >don't match. Using COUNTIF for CR & DR we can identify that there are 2 DRs
> >"not matched" (counts of 10 and 12 respectively).
> >
> >Is this sufficient? Or do you want to compare to find balancing CR/DR values?
> >
> >> i am having the following data in A1:B22
> >>
> >[quoted text clipped - 36 lines]
> >>
> >> -via135
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200607/1
>

[via135 via OfficeKB.com]

i think all value of 1000 may be confusing
ok let me change my example

COL A COL B

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 countif formula in COL C
to mark some flag like "matched", "unmatched"

hope that explained the things better!!

-via135

Toppers wrote:
>=Countif(B1:B22,"CR") and =Countif(B1:B22,"DR") will give respective overall
>counts which are 10 and 12 so (as I said in my previous post) indicates 2
>"extraneeous" DRs. In this case it suggests either a DR should be a CR to get
>11/11 balance or 2 CRs are missing: there is no way (that I can think of) of
>determining which situation is correct.
>
>> yes.. i want the correct "countif" formula to list out the
>> overall unmatched (dr/cr) items list
>[quoted text clipped - 13 lines]
>> >>
>> >> -via135

--
Message posted via http://www.officekb.com

[Toppers]

I understand but haven't found a suitable solution!

"via135 via OfficeKB.com" wrote:

> i think all value of 1000 may be confusing
> ok let me change my example
>
> COL A COL B
>
> 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 countif formula in COL C
> to mark some flag like "matched", "unmatched"
>
> hope that explained the things better!!
>
> -via135
>
> Toppers wrote:
> >=Countif(B1:B22,"CR") and =Countif(B1:B22,"DR") will give respective overall
> >counts which are 10 and 12 so (as I said in my previous post) indicates 2
> >"extraneeous" DRs. In this case it suggests either a DR should be a CR to get
> >11/11 balance or 2 CRs are missing: there is no way (that I can think of) of
> >determining which situation is correct.
> >
> >> yes.. i want the correct "countif" formula to list out the
> >> overall unmatched (dr/cr) items list
> >[quoted text clipped - 13 lines]
> >> >>
> >> >> -via135
>
> --
> Message posted via http://www.officekb.com
>

[via135 via OfficeKB.com]

no problem!!
thks for responding!!

-via135

Toppers wrote:
>I understand but haven't found a suitable solution!
>
>> i think all value of 1000 may be confusing
>> ok let me change my example
>[quoted text clipped - 65 lines]
>> >> >>
>> >> >> -via135

--
Message posted via http://www.officekb.com