Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
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 Wrote: 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 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=561822 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
"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($C10,$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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
listing unmatched items!
thks Max for the wonderful stuff.. lot more than what i expected..!! thks a lot!! -via135 Max Wrote: "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($C10,$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 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=561822 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
flagging unmatched items!!! | Excel Worksheet Functions | |||
Listing all items only once | Excel Discussion (Misc queries) | |||
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) |