Atul, Unfortunately that will not work for my application. What I'm trying to do is dynamically create this list without the user's intervention. Is this possible?
Thanks, Dave On Nov 6, 6:46 am, "SINGHAL, ATUL." <[EMAIL PROTECTED]> wrote: > Hi Dave, > > Using advanced filter can solve your problem. Use Data-Filter-Advanced > Filter (Unique records only must be ticked) and give the reference of the > cell you wanna place your filtered data. Then you can sort the data. > File is enclosed for your reference. > > Regards > CA. Atul Singhal > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 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 > > This Message was sent from Indian Oil Messaging Gateway, New Delhi, India. > The information contained in this electronic message and any attachments to > this message are intended for the exclusive use of the addressee(s) and may > contain proprietary, confidential or privileged information. If you are not > the intended recipient, you should not disseminate, distribute or copy this > e-mail. Please notify the sender immediately and destroy all copies of this > message and any attachments. > > > > Dave.xls > 18KViewDownload- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---