Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria & Count if Equals 0 | Excel Worksheet Functions | |||
Sumproduct with mulitple criteria containing equals and or's | Excel Worksheet Functions | |||
countif column d equals x and column a noblank | Excel Worksheet Functions | |||
custom filter does not work when selecting 'equals' X AND 'equals' | Excel Discussion (Misc queries) | |||
how to write 'not equals blank' criteria in SUMIF | Excel Worksheet Functions |