software

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

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

quot;Louisequot; wrote:

gt; I have a huge worksheet, consisting of codes in Column A, a product name in
gt; Column B and a cost price of the item in Column C. Each product some times
gt; appears more than once as they may have more than one cost price.
gt;
gt; How do I filter the list so it will only show me one record of each product
gt; rather than several, depending on the number of cost prices each item has?
gt;
gt; Hope this makes sense??
gt;
gt; Thank you.
gt;
gt; Louise

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

quot;Duke Careyquot; wrote:

gt; Since you don't care about column C, just copy the HEADERS for columns A amp; B
gt; to an empty spot on your sheet, then use Data-gt;Filter-gt;Advanced Filter
gt;
gt; Select Copy to another location, check Unique records only, then select the
gt; Copy to box and indicate the 2 cellswhere you copied the headings from
gt; columns A amp; B. Click on OK amp; Excel will generate the list for you
gt;
gt;
gt; quot;Louisequot; wrote:
gt;
gt; gt; Is the easiest way of doing this to perform an Advanced Filter on all the
gt; gt; data to show only those three columns and then adding sub totals, asking it
gt; gt; to insert a sub total at every change in the Product name??
gt; gt; Louise
gt; gt;
gt; gt; quot;Louisequot; wrote:
gt; gt;
gt; gt; gt; I have a huge worksheet, consisting of codes in Column A, a product name in
gt; gt; gt; Column B and a cost price of the item in Column C. Each product some times
gt; gt; gt; appears more than once as they may have more than one cost price.
gt; gt; gt;
gt; gt; gt; How do I filter the list so it will only show me one record of each product
gt; gt; gt; rather than several, depending on the number of cost prices each item has?
gt; gt; gt;
gt; gt; gt; Hope this makes sense??
gt; gt; gt;
gt; gt; gt; Thank you.
gt; gt; gt;
gt; gt; gt; Louise

Since you don't care about column C, just copy the HEADERS for columns A amp; B
to an empty spot on your sheet, then use Data-gt;Filter-gt;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 amp; B. Click on OK amp; Excel will generate the list for youquot;Louisequot; wrote:

gt; Is the easiest way of doing this to perform an Advanced Filter on all the
gt; data to show only those three columns and then adding sub totals, asking it
gt; to insert a sub total at every change in the Product name??
gt; Louise
gt;
gt; quot;Louisequot; wrote:
gt;
gt; gt; I have a huge worksheet, consisting of codes in Column A, a product name in
gt; gt; Column B and a cost price of the item in Column C. Each product some times
gt; gt; appears more than once as they may have more than one cost price.
gt; gt;
gt; gt; How do I filter the list so it will only show me one record of each product
gt; gt; rather than several, depending on the number of cost prices each item has?
gt; gt;
gt; gt; Hope this makes sense??
gt; gt;
gt; gt; Thank you.
gt; gt;
gt; gt; Louise

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:

www.peltiertech.com/Excel/Pivots/pivotstart.htmLouise wrote:
gt; They originally said they wanted to return at least one of the entries though
gt; from COlumn C, didn't really matter which one.
gt;
gt; I have since 'convinced' them that this is pointless really and created the
gt; advanced filter, as suggested below. Worked a treat.
gt;
gt; Thank you.
gt;
gt; Louise
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt;
gt;gt;Since you don't care about column C, just copy the HEADERS for columns A amp; B
gt;gt;to an empty spot on your sheet, then use Data-gt;Filter-gt;Advanced Filter
gt;gt;
gt;gt;Select Copy to another location, check Unique records only, then select the
gt;gt;Copy to box and indicate the 2 cellswhere you copied the headings from
gt;gt;columns A amp; B. Click on OK amp; Excel will generate the list for you
gt;gt;
gt;gt;
gt;gt;quot;Louisequot; wrote:
gt;gt;
gt;gt;
gt;gt;gt;Is the easiest way of doing this to perform an Advanced Filter on all the
gt;gt;gt;data to show only those three columns and then adding sub totals, asking it
gt;gt;gt;to insert a sub total at every change in the Product name??
gt;gt;gt;Louise
gt;gt;gt;
gt;gt;gt;quot;Louisequot; wrote:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;gt;I have a huge worksheet, consisting of codes in Column A, a product name in
gt;gt;gt;gt;Column B and a cost price of the item in Column C. Each product some times
gt;gt;gt;gt;appears more than once as they may have more than one cost price.
gt;gt;gt;gt;
gt;gt;gt;gt;How do I filter the list so it will only show me one record of each product
gt;gt;gt;gt;rather than several, depending on the number of cost prices each item has?
gt;gt;gt;gt;
gt;gt;gt;gt;Hope this makes sense??
gt;gt;gt;gt;
gt;gt;gt;gt;Thank you.
gt;gt;gt;gt;
gt;gt;gt;gt;Louise
gt;gt;gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html