String of sales codes to be broken into separate columns

[Guest]

This is my first time writing a macro as well as my first time posting on
here so I hope I am doing this right. I have these large spreadsheets with a
column of sales codes. From the query I ran, the sales code column contains
strings of sales codes that I would like to break out into individual
columns. This is what the data looks like now:

Sales Codes
1AA
1AA, WFG, XPD
1AA, WFG, XPC
1AB

And this would be what I would like to go to...

Sales Codes 1AA WFG XPD XPC 1AB
1AA 1AA
1AA, WFG, XPD 1AA WFG XPD
1AA, WFG, XPC 1AA WFG XPC
1AB
1AB

The macro would start with row 2, and look at the sales codes in the sales
code column and create columns for each of the sales codes it finds in the
sales code column. If it finds a sales code that already has a column created
for it, it will just copy that sales code into that column.

I feel like I could write the pseudocode for this, but I am not familiar
with the syntax. This is what I would say the code would look like:

While( salescode_column_cell(i) != null, i = 1, i++)
if(salescode_column_cell(i) != null)
take string of sales codes and get each 3 letter code
create columns for each unique sales code and put sales code in that
column and row
for sales codes with columns already created, put sales code in that
row/column

Okay, maybe I don't remember how to write pseudocode that well either. Any
help would be greatly appreciated. Thanks in advance!

-Kenneth

 

[Niek Otten]

You probably don't need a macro. Look at
Data>Text to columns
in the menu bar.
Either use separators (spaces and commas probably) or fixed with

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| This is my first time writing a macro as well as my first time posting on
| here so I hope I am doing this right. I have these large spreadsheets with a
| column of sales codes. From the query I ran, the sales code column contains
| strings of sales codes that I would like to break out into individual
| columns. This is what the data looks like now:
|
| Sales Codes
| 1AA
| 1AA, WFG, XPD
| 1AA, WFG, XPC
| 1AB
|
| And this would be what I would like to go to...
|
| Sales Codes 1AA WFG XPD XPC 1AB
| 1AA 1AA
| 1AA, WFG, XPD 1AA WFG XPD
| 1AA, WFG, XPC 1AA WFG XPC
| 1AB
| 1AB
|
| The macro would start with row 2, and look at the sales codes in the sales
| code column and create columns for each of the sales codes it finds in the
| sales code column. If it finds a sales code that already has a column created
| for it, it will just copy that sales code into that column.
|
| I feel like I could write the pseudocode for this, but I am not familiar
| with the syntax. This is what I would say the code would look like:
|
| While( salescode_column_cell(i) != null, i = 1, i++)
| if(salescode_column_cell(i) != null)
| take string of sales codes and get each 3 letter code
| create columns for each unique sales code and put sales code in that
| column and row
| for sales codes with columns already created, put sales code in that
| row/column
|
| Okay, maybe I don't remember how to write pseudocode that well either. Any
| help would be greatly appreciated. Thanks in advance!
|
| -Kenneth
|

 

[Guest]

Thanks Nick...That did separate the codes out to numbers, but I have about 10
sales codes..and each string may have 1-5 so I was hoping to get all the
codes into their proper columns. For example below I have two strings that
are similar "1AA, WFG, XPD" and "1AA, WFG, XPC"...out of this I would need 4
columns, but the text to columns thing only does 3. Thanks for your quick
reply and any further assistance you can give me!

-Kenneth