If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keep lines when one column equals certain criteria
Thanks for taking a look at this one.
I have a worksheet with invoice amounts, payments, voids and adjustments each on a separate line. Each line has an invoice reference in Col F. If Column K has "Adj" in it I would like for it to keep all the corresponding lines for that invoice. Col F Col K 12344 12345 12345 12345 Adj 123456 123456 Adj 123457 123457 I would want it to keep all the lines for inv. 12345 and all the lines for 123456 Thanks |
#2
|
|||
|
|||
Keep lines when one column equals certain criteria
Think it would have been better/far easier for responders to grasp your issue
if you had displayed the sample desired results for the sample data that you posted (the simple get-me-from "A" to "B" approach) Nonetheless here's one thought, focusing on this spec line of yours: .. keep all the lines for inv. 12345 and all the lines for 123456 You could flag all the requisite lines using this in say L2: =IF(SUMPRODUCT((F$2:F$10=F2)*(K$2:K$10="Adj"))0," x","") Copy down to L10. Then apply autofilter to col L, choose: x to filter out the required result lines. Copy the filtered results, paste elsewhere (on another sheet) as desired. Modify the ranges to suit the actual extents of your data. Any joy? hit the YES below. -- Max Singapore --- "Donna" wrote: Thanks for taking a look at this one. I have a worksheet with invoice amounts, payments, voids and adjustments each on a separate line. Each line has an invoice reference in Col F. If Column K has "Adj" in it I would like for it to keep all the corresponding lines for that invoice. Col F Col K 12344 12345 12345 12345 Adj 123456 123456 Adj 123457 123457 I would want it to keep all the lines for inv. 12345 and all the lines for 123456 Thanks |
#3
|
|||
|
|||
Keep lines when one column equals certain criteria
Yes, very joyful. Sorry about not formulating my question better, but you
did get it to do what I wanted it too. Thank you very much. Donna "Max" wrote: Think it would have been better/far easier for responders to grasp your issue if you had displayed the sample desired results for the sample data that you posted (the simple get-me-from "A" to "B" approach) Nonetheless here's one thought, focusing on this spec line of yours: .. keep all the lines for inv. 12345 and all the lines for 123456 You could flag all the requisite lines using this in say L2: =IF(SUMPRODUCT((F$2:F$10=F2)*(K$2:K$10="Adj"))0," x","") Copy down to L10. Then apply autofilter to col L, choose: x to filter out the required result lines. Copy the filtered results, paste elsewhere (on another sheet) as desired. Modify the ranges to suit the actual extents of your data. Any joy? hit the YES below. -- Max Singapore --- "Donna" wrote: Thanks for taking a look at this one. I have a worksheet with invoice amounts, payments, voids and adjustments each on a separate line. Each line has an invoice reference in Col F. If Column K has "Adj" in it I would like for it to keep all the corresponding lines for that invoice. Col F Col K 12344 12345 12345 12345 Adj 123456 123456 Adj 123457 123457 I would want it to keep all the lines for inv. 12345 and all the lines for 123456 Thanks |
Thread Tools | |
Display Modes | |
|
|