Some invoices only have 2 lines when they are void but some have several
lines. How would I set up a formula to remove all of the lines except the
one that says receipt when the invoice if void?
2650741 $1,484,822.62 Inv $1,484,822.62
2650741 -$222,724.00 Receipt -$222,724.00
2650741F -$1,262,098.62 Finance -$1,262,098.62
2650741 -$1,484,822.62 Void -$1,484,822.62
2650741F -$1,262,098.62 Finance $1,262,098.62
2650741 $222,724.00 adj $222,724.00
Donna

One crack at this ..
Assume your source data as posted in A2:D2 down to row 100 (say)
Put in F2, normal ENTER to confirm will do:
=IF(AND(C2="Receipt",ISNUMBER(MATCH(1,INDEX(($A$2: $A$100=A2)*($C$2:$C$100="Void"),),0))),ROW(),"")
This frames up the criteria bit of it
Now for the extract bit ..
Put in G2:
=IF(ROWS($1:1)COUNT($F:$F),"",INDEX(A:A,SMALL($F: $F,ROWS($1:1))))
Copy G2 to J2. Then select F2:J2, copy down to row 100 (cover the max
expected extent of source data). Cols G to J will return only the required
lines, neatly packed at the top. voila? celebrate it, hit the YES below
--
Max
Singapore
---
"Donna" wrote:
Some invoices only have 2 lines when they are void but some have several
lines. How would I set up a formula to remove all of the lines except the
one that says receipt when the invoice if void?
2650741 $1,484,822.62 Inv $1,484,822.62
2650741 -$222,724.00 Receipt -$222,724.00
2650741F -$1,262,098.62 Finance -$1,262,098.62
2650741 -$1,484,822.62 Void -$1,484,822.62
2650741F -$1,262,098.62 Finance $1,262,098.62
2650741 $222,724.00 adj $222,724.00
Donna