Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
hi! i am having a list as under in A1 : B8 with Col Headings where A3,A7,A8,B2,B4B5, & B6 are blank cells: debit credit 100 blank blank 100 300 blank 400 blank 500 blank blank 300 blank 200 what i want is to filter the list and findout the individual debit and credit outstandings and the result should be as under: debit credit 400 blank 500 blank blank 200 help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
What are you trying to filter? I don't see any pattern in the result
you've shown. via135 wrote: hi! i am having a list as under in A1 : B8 with Col Headings where A3,A7,A8,B2,B4B5, & B6 are blank cells: debit credit 100 blank blank 100 300 blank 400 blank 500 blank blank 300 blank 200 what i want is to filter the list and findout the individual debit and credit outstandings and the result should be as under: debit credit 400 blank 500 blank blank 200 help pl?! -via135 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
hi Debra! the pattern is A2 (100) offsetted against B3 (100), A4 (300) against B7 (300). Now the unmatched items are in rows 5,6 & 8. I want to filter out those three records! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
if Debit amounts are in Col A and Credit amounts are in Col B, you can find out the outstanding Balance in Col C in following way. Col A........Col B........Col C Debit.......Credit.......Balance amount....blank.......=C2+A3-B3 copy this function down in Col C this format will ascertain outstanding amount in total not by individual entries. so far as filtering is concerned i dont think this can be done through filtering. -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
Add a staging column in col C, with following formula:
=COUNTIF($B$2:$B$8,$A5)+COUNTIF($A$2:$A$8,$B5) Then autofilter 0 values in that column. See example: http://cjoint.com/?exkwMVpDQB HTH -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH
Anyway, won't work if you have several equal amounts -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
hi! ofcourse..for each entry i have the ref in another coloumn say in COL "D"!!! i've attached the sample for your reference! help pl! -via135 Ardus Petus Wrote: Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH Anyway, won't work if you have several equal amounts -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 +-------------------------------------------------------------------+ |Filename: example.txt | |Download: http://www.excelforum.com/attachment.php?postid=4681 | +-------------------------------------------------------------------+ -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
Try this: (works by me)
=SUMPRODUCT((A2<"")*(B$2:B$10=A2)*(D$2:D$10=D2)+( B2<"")*(A$2:A$10=B2)*(D$2 :D$10=D2)) HTH -- AP "via135" a écrit dans le message de ... hi! ofcourse..for each entry i have the ref in another coloumn say in COL "D"!!! i've attached the sample for your reference! help pl! -via135 Ardus Petus Wrote: Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH Anyway, won't work if you have several equal amounts -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 +-------------------------------------------------------------------+ |Filename: example.txt | |Download: http://www.excelforum.com/attachment.php?postid=4681 | +-------------------------------------------------------------------+ -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
Simpler solution:
=SUMPRODUCT((A$2:A$11+B$2:B$11=A2+B2)*(D$2:D$11=D2 )) Select the "1" results ("2"s are balanced) HTH -- AP "via135" a écrit dans le message de ... hi! ofcourse..for each entry i have the ref in another coloumn say in COL "D"!!! i've attached the sample for your reference! help pl! -via135 Ardus Petus Wrote: Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH Anyway, won't work if you have several equal amounts -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 +-------------------------------------------------------------------+ |Filename: example.txt | |Download: http://www.excelforum.com/attachment.php?postid=4681 | +-------------------------------------------------------------------+ -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filtering
waw..! thks AP! thks so much!! this is what i exactly want!!! -via135 Ardus Petus Wrote: Simpler solution: =SUMPRODUCT((A$2:A$11+B$2:B$11=A2+B2)*(D$2:D$11=D2 )) Select the "1" results ("2"s are balanced) HTH -- AP "via135" a écrit dans le message de ... hi! ofcourse..for each entry i have the ref in another coloumn say in COL "D"!!! i've attached the sample for your reference! help pl! -via135 Ardus Petus Wrote: Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH Anyway, won't work if you have several equal amounts -- AP "via135" a écrit dans le message de ... hi! reminding for some help..??!! -via135 via135 Wrote: hi! i am particular about individual datewise outstanding entries where the dates are in COL "C". it's ok if there is any other way other than filtering? help pl?! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 +-------------------------------------------------------------------+ |Filename: example.txt | |Download: http://www.excelforum.com/attachment.php?postid=4681 | +-------------------------------------------------------------------+ -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=535072 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance filtering with multiple conditons | Excel Discussion (Misc queries) | |||
filtering | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions | |||
avanced filtering for latest date | Excel Discussion (Misc queries) | |||
Using Filtering | Excel Discussion (Misc queries) |