Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
=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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
flagging unmatched items!!!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLAP Pivot table - How to show items with no data ? | Excel Worksheet Functions | |||
Lookup items and have picture appear together. | Excel Worksheet Functions | |||
Pareto's Law and ABCD ranking of items | Excel Discussion (Misc queries) | |||
multiple items in database | Excel Worksheet Functions | |||
Computing totals for tax and non-tax items | Excel Worksheet Functions |