Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting recuring data
I have a spreadsheet where there is one invoice number with muliple
lines. The shipping charges are recorded in each row even if there was only one charge. There are also invoice numbers with multple lines and multple shipping charges. I want to delete the rows where there was only one shipping charge but keep the row where there were multiples. Current Spreadsheet Print Job Order Id # ordered Cost Total Cost Shipping 34525 2271 5 0 175.54 48.04 34725 2271 65 0 175.54 48.04 34518 2271 1 8 175.54 48.04 24203 6910 500 0 1269.26 201.73 24203 6910 500 0 1269.26 49.8 24203 6910 500 0 1269.26 21.42 What I would like to get back 34525 2271 5 0 175.54 48.04 34725 2271 65 0 175.54 0 34518 2271 1 8 175.54 0 24203 6910 500 0 1269.26 201.73 24203 6910 500 0 1269.26 49.8 24203 6910 500 0 1269.26 21.42 and then ultimately I would like all the shipping costs for order 6910 to be added so I can use this information in my pivot table. Thank you for any help you can provide. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting recuring data
I am assuming that your data is in columns A to F, and that it starts in Row 2.
In G2, enter the formula =IF(SUMPRODUCT(($B$2:B2=B2)*($F$2:F2=F2))1,0,F2) and copy down to match your data table. Then copy column G, and paste special values into column F, then delete column G. HTH, Bernie MS Excel MVP "kjsmom98" wrote in message ... I have a spreadsheet where there is one invoice number with muliple lines. The shipping charges are recorded in each row even if there was only one charge. There are also invoice numbers with multple lines and multple shipping charges. I want to delete the rows where there was only one shipping charge but keep the row where there were multiples. Current Spreadsheet Print Job Order Id # ordered Cost Total Cost Shipping 34525 2271 5 0 175.54 48.04 34725 2271 65 0 175.54 48.04 34518 2271 1 8 175.54 48.04 24203 6910 500 0 1269.26 201.73 24203 6910 500 0 1269.26 49.8 24203 6910 500 0 1269.26 21.42 What I would like to get back 34525 2271 5 0 175.54 48.04 34725 2271 65 0 175.54 0 34518 2271 1 8 175.54 0 24203 6910 500 0 1269.26 201.73 24203 6910 500 0 1269.26 49.8 24203 6910 500 0 1269.26 21.42 and then ultimately I would like all the shipping costs for order 6910 to be added so I can use this information in my pivot table. Thank you for any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fetching data and deleting original data | New Users to Excel | |||
MS2007 - deleting filtered data, deletes non-visible data too | Excel Discussion (Misc queries) | |||
Deleting data | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) |