Hi, There certainly is an array formula, that does give you unique values, except that it shows FALSE for duplicate ones.
Use the following formula... =IF(COUNTIF(INDIRECT("A1:A"&ROW()),A2)=1,A2) Assume that col A contains names of Capitals And States (cell A1 is column header). Now Select an equal range in column B and input the formula, end the formula with CTRL+SHIFT+ENTER. And you get the unique values from column A. Ragards, Ajit -----Original Message----- From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, November 06, 2008 2:04 AM To: MS Excel & VBA Macros Subject: $$Excel-Macros$$ Use of Array Formula or other method to create deduplicated list of text values? 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 -~----------~----~----~----~------~----~------~--~---