parse comma separated text to multiple cells and remove duplicates

[Guest]

hi all,

i seem to have been a bit stupid about this before: this was meant to be a
questions to the group and NOT a suggestion to microsoft. i also put it into
the wrong cetegory. apologies folks, i haven't been here in a while. sorry!

MY QUESTION:
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

p.s. any way i can remove my previous post in the "general questions" section?

 

[Roger Govier]

Hi

Take a look at the Data Text to Columns Wizard.
Assuming your comma separate words are in column B, ensure you have
sufficient blanks columns to the right of column B to account for the
maximum number of words likely to be found in a cell.
Mark column B then
Data>Text to Columns>Delimited>tick Comma>Next>Finish

 

[Guest]

hi roger,

thanks for your reply. i have already tried the wizard. this results in the
following:

2 RFID Shopping, security, inventory --> Shopping security inventory

across columns. what i want is:

Shopping
security
inventory

this is more of a "text to cells in a colum" function i am looking for. the
layout in my OP was indicative of how i want it. no wizard for that i expect!

thanks nonetheless!

 

[Guest]

Once you do the Text to columns function, then you can run TRANSPOSE to
convert horizontal data to vertical. Here's some info:
http://www.ozgrid.com/Services/excel-copy-formulas.htm

 

[Guest]

hi dave,

you are right, using transpose does get my data into the layout (into rows
in a column). this, however, is only a usable solution if you are not dealing
with many line items. being a manual process you describe, i would have to do
this many times. i want something a bit more automated (or button triggered).
then i still have to find duplicates (which i guess is a second problem).

check out the "List Every Item Only Once in a List Box" items here:
http://fcfhsp.customer.netspace.net.au/xlhome.htm

this gives you a bit of an idea of what i am after post parsing.

thanks,
d

 

[Ken Johnson]

Hi doon,

This macro seems to work.

Edit the first line to get the list where you want it. The address
(currently B1) will become the heading "KEY WORDS:"
Select the range of cells to parse before running.
Try it out on a copy of your data.

Public Sub Uniques_from_comma()
'Change "B1" next line to suit your needs
Const strDestination As String = "B1"
Dim arrSplit() As String
Dim strAll As String
Dim rngCell As Range
Dim I As Long
Dim J As Long
For Each rngCell In Selection
If rngCell.Value <> "" Then
strAll = strAll & ", " & rngCell.Value
End If
Next rngCell
strAll = Right(strAll, Len(strAll) - 2)
arrSplit = Split(strAll, ", ")
For I = 0 To UBound(arrSplit) - 1
For J = I + 1 To UBound(arrSplit)
If arrSplit(J) = arrSplit(I) Then
arrSplit(J) = ""
End If
Next J
Next I
J = 0
Range(strDestination).Value = "KEY WORDS:"
For I = 0 To UBound(arrSplit)
If arrSplit(I) <> "" Then
J = J + 1
Range(strDestination).Offset(J, 0) = arrSplit(I)
End If
Next I
Range(Range(strDestination).Offset(1, 0), _
Range(strDestination).Offset(J, 0)).Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Ken Johnson

 

[Ken Johnson]

Oops!

You get an error after editing strDestination from B1, which is now
fixed...
Public Sub Uniques_from_comma()
'Change "B1" next line to suit your needs
Const strDestination As String = "B1"
Dim arrSplit() As String
Dim strAll As String
Dim rngCell As Range
Dim I As Long
Dim J As Long
For Each rngCell In Selection
If rngCell.Value <> "" Then
strAll = strAll & ", " & rngCell.Value
End If
Next rngCell
strAll = Right(strAll, Len(strAll) - 2)
arrSplit = Split(strAll, ", ")
For I = 0 To UBound(arrSplit) - 1
For J = I + 1 To UBound(arrSplit)
If arrSplit(J) = arrSplit(I) Then
arrSplit(J) = ""
End If
Next J
Next I
J = 0
Range(strDestination).Value = "KEY WORDS:"
For I = 0 To UBound(arrSplit)
If arrSplit(I) <> "" Then
J = J + 1
Range(strDestination).Offset(J, 0) = arrSplit(I)
End If
Next I
Range(Range(strDestination).Offset(1, 0), _
Range(strDestination).Offset(J, 0)).Sort _
Key1:=Range(strDestination).Offset(1, 0), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Ken Johnson

 

[Dave Peterson]

You have another response at your other thread.

Please don't multipost. You either wasted my time or the time of the people who
responded in this thread.

 

[Guest]

no way! you are a rock star!

i have it hooked up to be triggered by a button. now all i have to do is
figure out if i can hard code a range (a column) and first clear the output
column (B1) before writing into it.

a million thanks!
doon

 

[Guest]

hi dave,

that wasn't my intention. i mis-posted in the general section (and put it in
as a suggestion rather than a question). there was no way to edit the post or
delete it. after waiiting a bit, i was concerned that i would not get any
responses. hence, i posted in the worksheet area with a better subject title.

i just responded to you in the other thread. your hellp is appreciated.

my apologies again.
don

 

[Guest]

got it working. i am thrilled. who would have thought that an ex-visual
designer could get so excited about excel?

 

[Dave Peterson]

You could have posted a followup telling readers to ignore this post because you
posted in another newsgroup.