Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filtering.....
I have a huge worksheet, consisting of codes in Column A, a product name in
Column B and a cost price of the item in Column C. Each product some times appears more than once as they may have more than one cost price. How do I filter the list so it will only show me one record of each product rather than several, depending on the number of cost prices each item has? Hope this makes sense?? Thank you. Louise |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filtering.....
Is the easiest way of doing this to perform an Advanced Filter on all the
data to show only those three columns and then adding sub totals, asking it to insert a sub total at every change in the Product name?? Louise "Louise" wrote: I have a huge worksheet, consisting of codes in Column A, a product name in Column B and a cost price of the item in Column C. Each product some times appears more than once as they may have more than one cost price. How do I filter the list so it will only show me one record of each product rather than several, depending on the number of cost prices each item has? Hope this makes sense?? Thank you. Louise |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filtering.....
They originally said they wanted to return at least one of the entries though
from COlumn C, didn't really matter which one. I have since 'convinced' them that this is pointless really and created the advanced filter, as suggested below. Worked a treat. Thank you. Louise "Duke Carey" wrote: Since you don't care about column C, just copy the HEADERS for columns A & B to an empty spot on your sheet, then use Data-Filter-Advanced Filter Select Copy to another location, check Unique records only, then select the Copy to box and indicate the 2 cellswhere you copied the headings from columns A & B. Click on OK & Excel will generate the list for you "Louise" wrote: Is the easiest way of doing this to perform an Advanced Filter on all the data to show only those three columns and then adding sub totals, asking it to insert a sub total at every change in the Product name?? Louise "Louise" wrote: I have a huge worksheet, consisting of codes in Column A, a product name in Column B and a cost price of the item in Column C. Each product some times appears more than once as they may have more than one cost price. How do I filter the list so it will only show me one record of each product rather than several, depending on the number of cost prices each item has? Hope this makes sense?? Thank you. Louise |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filtering.....
Since you don't care about column C, just copy the HEADERS for columns A & B
to an empty spot on your sheet, then use Data-Filter-Advanced Filter Select Copy to another location, check Unique records only, then select the Copy to box and indicate the 2 cellswhere you copied the headings from columns A & B. Click on OK & Excel will generate the list for you "Louise" wrote: Is the easiest way of doing this to perform an Advanced Filter on all the data to show only those three columns and then adding sub totals, asking it to insert a sub total at every change in the Product name?? Louise "Louise" wrote: I have a huge worksheet, consisting of codes in Column A, a product name in Column B and a cost price of the item in Column C. Each product some times appears more than once as they may have more than one cost price. How do I filter the list so it will only show me one record of each product rather than several, depending on the number of cost prices each item has? Hope this makes sense?? Thank you. Louise |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advanced Filtering.....
Another option is to create a pivot table from the data, with Code and
Product in the row area, and Price in the data area, as Average of Price, or Max of Price. There are pivot table instructions in Excel's Help, and Jon Peltier has instructions and links: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Louise wrote: They originally said they wanted to return at least one of the entries though from COlumn C, didn't really matter which one. I have since 'convinced' them that this is pointless really and created the advanced filter, as suggested below. Worked a treat. Thank you. Louise "Duke Carey" wrote: Since you don't care about column C, just copy the HEADERS for columns A & B to an empty spot on your sheet, then use Data-Filter-Advanced Filter Select Copy to another location, check Unique records only, then select the Copy to box and indicate the 2 cellswhere you copied the headings from columns A & B. Click on OK & Excel will generate the list for you "Louise" wrote: Is the easiest way of doing this to perform an Advanced Filter on all the data to show only those three columns and then adding sub totals, asking it to insert a sub total at every change in the Product name?? Louise "Louise" wrote: I have a huge worksheet, consisting of codes in Column A, a product name in Column B and a cost price of the item in Column C. Each product some times appears more than once as they may have more than one cost price. How do I filter the list so it will only show me one record of each product rather than several, depending on the number of cost prices each item has? Hope this makes sense?? Thank you. Louise -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filtering | Excel Discussion (Misc queries) | |||
Advanced Auto Filtering | Excel Worksheet Functions | |||
Advanced Filtering | Excel Worksheet Functions | |||
Advanced Filtering | Excel Worksheet Functions | |||
extracting numbers with no more than 8-digits using advanced filtering | Excel Worksheet Functions |