Ladies & Gents: I’m an experienced excel user (10+ years) and have encountered a problem that I cannot figure out. I’m creating a tool where there is a free text area where the users will input a list of locations (up to 60 values that can repeat and are in no particular order - some cells can be blank as well). Various analysis will be done on this data on a row by row basis and I need to create a list of these locations which is deduplicated and alphabetized on another sheet for summary calculations. I know that utilizing crystal reports one can utilize their array formulas to do a similar thing, but I cannot figure out how to do this in excel.
Below is an example of the data: Washington, DC Arlington, VA Washington, DC Washington, DC Washington, DC Cary, NC Paris, FR London, UK Raleigh, NC Raleigh, NC Atlanta, GA Raleigh, NC The output I’m looking for is: Arlington, VA Atlanta, GA Cary, NC London, UK Paris, FR Raleigh, NC Washington, DC Ideally I’d like this to be formula driven rather than macro driven. I know that if I do my summary in a pivot table I can accomplish much the same thing but would like to avoid that as well. Any help would be greatly appreciated!! Thanks, Dave --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---