As per the comments at http://chandoo.org/wp/2010/09/27/rem...-pivot-tables/ It’s worth pointing out that the pivot table approach might significantly bloat the size of a file, because ALL your data is duplicated within the pivot cache…which could be many many megabytes even if your pivot summary only has a few items.
So you might want to have your data stored in another workbook, and just point the pivottable to that workbook. But this will still pull all the data in the entire table through, even though you only want a distinct list of items.
So you could just pull in the column you are interested in (i.e. your ‘product name’ column), and not all the data from the entire table. But even this will pull through many duplicate items, even though your pivot table will only be displaying a list of the distinct items.
Another approach is to use a ‘Select Distinct’ or a 'group by' SQL query from microsoft query to just returns a list of distinct (i.e. non-duplicate) names , and nothing else. This is pretty easy to set up using Microsoft Query when you know how…you just have to make sure that your raw data table is in a form that MS Query can see it...although it precludes you having your list in the same spreadsheet due to memory leak issues (see the comments at http://www.excelhero.com/blog/2010/0...se-lookup.html )