flagging unmatched items!!!

  • Thread starter Thread starter via135 via OfficeKB.com
  • Start date Start date
V

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
 
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?
 
yes.. i want the correct "countif" formula to list out the
overall unmatched (dr/cr) items list
by flaging in col C...??

-via135
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
Click to expand...
[quoted text clipped - 36 lines]
-via135
Click to expand...
Click to expand...
 
=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 said:
yes.. i want the correct "countif" formula to list out the
overall unmatched (dr/cr) items list
by flaging in col C...??

-via135
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
Click to expand...
[quoted text clipped - 36 lines]
-via135
Click to expand...
Click to expand...
Click to expand...
 
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
=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
Click to expand...
[quoted text clipped - 13 lines]
Click to expand...
 
I understand but haven't found a suitable solution!

via135 via OfficeKB.com said:
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
=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
Click to expand...
[quoted text clipped - 13 lines]
-via135
Click to expand...
Click to expand...
Click to expand...
 
Back
Top