Need Help with copying list

[AStengel]

I have a workbook with two sheets (A and B) on sheet A I have a column that has lots of Locations, some of which repeat. On sheet B I would like to have a column that auto-populates with the Locations from sheet A without repeating them. How do I do this?

[#2]

if you have the most current version of Excel, the UNIQUE function will serve your purposes perfectly.

[#3]

@mathetes when I use this function I get a spill error

[#4]

@AStengel 

If you're getting the SPILL error that's a good thing. It means the function is working.

You just need to clear all the space below so that your full list of unique locations has room to get listed. (And clear to the right if you're looking for more than the location names)

[#5]

@mathetes I got it to work properly but when I format it as a table (so I can sort it) it no longer works.

[#6]

@mathetes Also I don't think this function is doing what I desire. It is only showing me locations that appeared once on Sheet A. I want to see every location on Sheet A but only have them on Sheet B once, instead of multiple times.

[#7]

@AStengel 

You do realize that without your actual spreadsheets, I'm working in the dark, so to speak.

1. the UNIQUE function will indeed give you on Sheet B one instance of each name that appears on Sheet A, regardless of how many times it appears on Sheet A. That is, it will do what you're seeking. Check how you've entered it.
2. If you want to sort the results and then make them part of a table, you need I think to do a Copy...Paste Special...values only.