filtering

[via135]

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]

What are you trying to filter? I don't see any pattern in the result
you've shown.

 

[via135]

hi Debra!

the pattern is A2 (100) offsetted against B3 (100), A4 (300) agains
B7
(300). Now the unmatched items are in rows 5,6 & 8. I want to filte
out those three records!

-via13

 

[starguy]

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.

 

[via135]

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]

hi!

reminding for some help..??!!

-via135

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

 

[Ardus Petus]

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

 

[Ardus Petus]

Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH


Anyway, won't work if you have several equal amounts

 

[via135]

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

Ooops! Wrong example - see : http://cjoint.com/?exkNll3UwH


Anyway, won't work if you have several equal amounts

--
AP

"via135" <[email protected]> a écrit
dans
le message de

+-------------------------------------------------------------------+
|Filename: example.txt |
|Download: http://www.excelforum.com/attachment.php?postid=4681 |
+-------------------------------------------------------------------+

 

[Ardus Petus]

Try this: (works by me)
=SUMPRODUCT((A2<>"")*(B$2:B$10=A2)*(D$2$10=D2)+(B2<>"")*(A$2:A$10=B2)*(D$2
$10=D2))

HTH

 

[Ardus Petus]

Simpler solution:
=SUMPRODUCT((A$2:A$11+B$2:B$11=A2+B2)*(D$2$11=D2))

Select the "1" results ("2"s are balanced)

HTH

 

[via135]

waw..!

thks AP!
thks so much!!
this is what i exactly want!!!

-via135