Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String of sales codes to be broken into separate columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String of sales codes to be broken into separate columns
You probably don't need a macro. Look at
DataText to columns in the menu bar. Either use separators (spaces and commas probably) or fixed with -- Kind regards, Niek Otten Microsoft MVP - Excel "Kenneth" wrote in message ... | 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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String of sales codes to be broken into separate columns
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 "Niek Otten" wrote: You probably don't need a macro. Look at DataText to columns in the menu bar. Either use separators (spaces and commas probably) or fixed with -- Kind regards, Niek Otten Microsoft MVP - Excel "Kenneth" wrote in message ... | 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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
Separate a FirstLast text string into two columns | Excel Discussion (Misc queries) | |||
Breaking up a string into separate columns | Excel Worksheet Functions | |||
projected sales forecast broken down into an hourly rate not linea | Excel Discussion (Misc queries) | |||
String manipulation from one cell broken up into multiple columns | Excel Programming |