parse comma separated text to multiple cells and remove duplicates
 

hi all,

i seem to have been a bit stupid about this befo 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?

parse comma separated text to multiple cells and remove duplicates
 

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
DataText to ColumnsDelimitedtick CommaNextFinish

--
Regards

Roger Govier


"doon" wrote in message
...
hi all,

i seem to have been a bit stupid about this befo 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?

parse comma separated text to multiple cells and remove duplic
 

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!

parse comma separated text to multiple cells and remove duplic
 

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


--
Brevity is the soul of wit.


"doon" wrote:

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!

parse comma separated text to multiple cells and remove duplic
 

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 he
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

parse comma separated text to multiple cells and remove duplicates
 

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

parse comma separated text to multiple cells and remove duplicates
 

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

parse comma separated text to multiple cells and remove duplicates
 

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.

doon wrote:

hi all,

i seem to have been a bit stupid about this befo 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?

--

Dave Peterson

parse comma separated text to multiple cells and remove duplic
 

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

parse comma separated text to multiple cells and remove duplic
 

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

parse comma separated text to multiple cells and remove duplic
 

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

parse comma separated text to multiple cells and remove duplic
 

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



doon wrote:

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

--

Dave Peterson