Dave, see attached workbook. May be this will help. _________________________________________________________________________________________________ "There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know."
________________________________
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: MS Excel & VBA Macros <excel-macros@googlegroups.com>
Sent: Thursday, 6 November, 2008 2:03:30 AM
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
Add more friends to your messenger and enjoy! Go to
http://messenger.yahoo.com/invite/
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
sorting data.xls
Description: MS-Excel spreadsheet
