Bank Statement Formula

[sajjjid]

I have attached the file.
in sheet1 i have the data.
in sheet2 i just need column B,E,F & G only.
from column E i dont want whatever start with DECS DR/
from column F & G instead of 0.00 it should be blank.
Thanks.

 

[Hui]

See Attached file

To understand this please read:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[sajjjid]

See Attached file

To understand this please read:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Thanks. Hui
I dont want anything starting from DECS from column B

 

[Hui]

Your question clearly says "i dont want whatever start with DECS DR/"?

So change for example
A2: =IF(COUNTIF(Sheet1!$E$1:$E$100,"<>"&"DECS DR/"&"*") < ROWS(D$2:$E2),"",INDEX(Sheet1!$B$1:$B$100,SMALL(IF(LEFT(Sheet1!$E$1:$E$100,8) <>"DECS DR/",ROW( Sheet2!$A$1:$A$100)),ROW(A2))))

to:
=IF(COUNTIF(Sheet1!$E$1:$E$100,"<>"&"DECS"&"*") < ROWS(D$2:$E2),"",INDEX(Sheet1!$B$1:$B$100,SMALL(IF(LEFT(Sheet1!$E$1:$E$100,4) <>"DECS",ROW( Sheet2!$A$1:$A$100)),ROW(A2))))

This is array entered with Ctrl+Shift+Enter
Then copy down

Similar for the other columns

 

[sajjjid]

Thanks hui. Its working now.