Another interesting macro challenge!

[danison]

Hi

I have yet another interesting challenge that I would greatl
appreciate assistance on. Thanks to Bob for his great help on my las
'challenge' query! ;-)


This time I have a list of words that are either in isolation, or hav
a comma and a number following them. There are also many duplicates o
these words in a list that can extend to be 3000 rows long!

Example of how the words may be listed:


dog
dog, 1234
dog, 4567
cat, 123
cat
cat, 45678
bird
bird, 12
bird, 4567
fish

What I am looking to do is via a macro, select the entire lis
(regardless of how long it is), and remove two things: (a) all of th
commas & numbers where they exist (b) All of the duplicates of th
words in the list

As such this sample list, once cleaned, would show in a separat
column:

dog
cat
bird
fish


I have attached a sample test list as an example.

Many thanks to everyone for your advice in advance!




Bil

 

[danison]

For some reason the file is a too big to attach although it is a simpl
text file.

Anyway, all I had was in column A (from A3) this list:

dog
dog, 1234
dog, 4567
cat, 123
cat
cat, 45678
bird
bird, 12
bird, 4567
fish
fish
fish, 456
fish, 123
poodle
poodle, 123
poodle, 456789


I then wanted it to convert to a clean list in drop into column C fro
C3

Cheers

Bil

 

[Tushar Mehta]

Turn on the macro recorder, carry out the steps below, turn off the
recorder, and clean up the XL generated code.

Use Data | Text to Columns... with comma and space as delimiters

Delete column B.

Insert a row before row 1.

Enter some text, say "F1" in A1.

Use Data | Filter > Advanced Filter... specify 'copy to B1' and 'unique
values only' to create a unique list in column B.

Cut column B. Paste over column A. Delete row 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[danison]

Great thanks, it works! Sorry for the novice question, but what is th
main XL generated code I need to clean up? Still learning!

Thanks

Bil

Attachment filename: test4.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=57093

 

[Mark]

This finds the text with the commas and numbers and
removes them:
in column B
=LEFT(A2,FIND(",",A2,1)-1)

Something like
=if( (LEFT(A2,FIND(",",A2,1)-1)<>error,LEFT(A2,FIND
(",",A2,1)-1),=A2)


I am still working on the other so you only have one
formula but someone with answer you soon. If not,
experiment a little.

 

[Tushar Mehta]

Glad the nudge in the right direction was enough to help you get the
solution yourself.

XL generates a lot of select's and activate's that are unnecessary. In
addition, it's range references are 'hard coded' and do not adapt to
changing (extending/shrinking) ranges. For some more see the
Excel/VBA/Beyond Excel's recorder page of my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions