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: [email protected] [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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
