an eye for the mundane

How to produce a list showing all the different values that appear in a column in Excel


here was my problem – I wanted to produce a list showing all the different values that a column had (so I could produce a vlookup table for it). Question was what was the easiest way to do this. The process didn’t have to be dynamic or based on formulas.
quick and dirty way: select the area of data which contains the various items and a numeric column next to it. [if there isn’t a numeric column next to it, copy the column to another worksheet and add your own numbers]. Then build a pivot table. Drag the data you want sorted in to the Row Field and the numerical field in to the Data Area. Magically you get a pivot table with a list of the unique values on the left. Now copy this and paste special (values) somewhere else in your sheet and delete the pivot table.
i checked my favourite excel site after I discovered this and of course it’s already there (method 5)
update: this post attracts more comment spam than any other one, so i’ve tuned comments off. very strange

Recent Comments
Follow Us

  

Archives