Finding distinct values in a column

[deadseasquirrels]

If I had a column of say zip codes, lets say thousands, and I have a lot of repeats. Is there a built in function in Excel that lets me find all the distinct zip codes and then outputs it into an array or string? I am pretty sure I can think of a way of doing this by just using VBA, but I was just wondering if there was some built in stuff to make it simplier.

[nagasrikanth]

I think the "subtotals" option is the only one that can serve u r purpose a little bit at least...

thanx & regards
Anu

[Ecniv]

You could look at a pivot table.
Select the data
Menu>Data>Pivot tables
Click yes
Click yes (to the range)
Click layout, drag n drop the field into data and row
(data should change to count - if not double click it and change it to count)
Click finish

It should give you a sheet with the list of values (unique) and a count of those values.

[JustinLabenne]

You could also try using the Advanced Filter option. It allows you to copy the uniques records to a new location.

[dannymking]

The Advanced Filter option in coding and into an array...

Dim arrVals()
  Range("A1:A" & Range("A1").End(xlDown).Row).AdvancedFilter xlFilterCopy, , Range("B1"), True
  arrVals = Range("C1:C" & Range("C1").End(xlDown).Row).Value

Be warned tho the resulting array is defined as a two dimensional array with the first dimension holding the row position and the second dimension holding the column like..

arrVals(1,1) the first cell
arrVals(2,1) the second cell
arrvals(n,1) the nth cell

[deadseasquirrels]

I'm not sure but I could swear that the advanced filter option worked when you did it manually, that is it would copy all the values that suited a particular filter, but then when I tried to do it in a macro it had a lot of problems.

I know for a fact that Excel has issues with you searching for a certain field and then moving that field and then doing a find next...it just can't handle that well. I ended up copying that field, doing a find next, and then deleting it later. If you delete it in between, like I said, it craps out.