HOW TO | *automatically* parse comma separated text to multiple ce

[Guest]

hi all,

i have searched the posts here, but have not really found the answer to my
particular problem.

i have an evaluation sheet i am building. in one column i am capturing key
words for a particular topc. these key words are comma separated. example:

1 TOPIC: KEYWORDS:
---------------------------------------------------------
2 RFID Shopping, security, inventory
3 UPC Shopping, inventory

what i want is twofold: i want to be able to parse out each word per cell
per row into a separate area (range of cells or other worksheet) with
duplicates removed. my result would be:

1 KEY WORDS:
---------------------------------------------------------
2 inventory
3 security
4 shopping

doesn't have to be alphabetized, but could be nice.

if this cannot be done on-the-fly but can be done with a macro (e.g. a
button i have to explicitly press to generate this), then great.

i have check out a ton of sites and see bits and pieces, but i am too green
with VBA to know how to make it work.

any help would be greatly appreciated!

thanks,
doon

Using:
- Excel 2003 (v.11 build 6113)
- WinXP SP2
- 512 MB RAM


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...7-796d1756dfd8&dg=microsoft.public.excel.misc

 

[Guest]

sorry, my title was too long and doesn't say anything useful in the postings
list view. it should read:

*automatically* parse comma separated text to multiple cells

although on retrospect, i would leave off the "automatic" part.

doon

 

[Dave Peterson]

You can select column B and do
data|text to columns
delimited
by comma

to separate the data into adjacent cells.

To get the list of unique key words, you could do that same thing, then build a
giant column (copy|pasting at the bottom of the giant column), then do
data|advanced filter|unique records only) to get the unique list.

In code, it could look like:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim myRng As Range
Dim iCol As Long
Dim DestCell As Range

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

CurWks.Range("b:b").Copy _
Destination:=NewWks.Range("A1")

With NewWks
.Range("a:a").TextToColumns Destination:=.Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

For iCol = 2 To .Range("a1").CurrentRegion.Columns.Count
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
.Range(.Cells(1, iCol), .Cells(.Rows.Count, iCol).End(xlUp)).Copy _
Destination:=DestCell
Next iCol

.Range("b1", .Cells(1, .Columns.Count)).EntireColumn.Delete
.Rows(1).Insert
.Range("a1").Value = "header"
.Range("a:a").AdvancedFilter action:=xlFilterCopy, _
copytorange:=.Range("b1"), unique:=True
.Columns(1).Delete
.Rows(1).Delete

'remove leading spaces!
.Columns(1).TextToColumns Destination:=.Range("A1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

.Columns(1).Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo

End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

hi dave,

thanks for your reply. i will give it a shot. i am trying a bit of code
someone else sent me, but i think i will end up taking bits from both. this
is really educational and helpful.

cheers!
d

 

[Dave Peterson]

But it's still not a good idea to multipost to several newsgroups.

If you really think that it's important to send the message to lots of
newsgroups, then send one message to all the groups (not an individual message
to each).