SandsB
Well-known Member
- Joined
- Feb 13, 2007
- Messages
- 705
- Office Version
- 365
- Platform
- Windows
I get data in column R that looks like this:
Listing: Variable1, Another Variable, Here is yet another one, Variable12, Last one.
Listing: Variable7, Here is yet another one, Variable12
Listing: Variable12
Listing: Variable1, Another Variable, New variable nobody ever saw before, Variable1
Some (most) rows have nothing in column R. "Listing:" is sort of a heading and it only appears if there are variables. If there are no variables the cell is completely empty. When there's more than one variable, they're separated by commas. I know how to split up the data by using the commas. What I can't figure out is how to make each variable that's in the cell populate it's own column starting in row U. So with the examples of the 5 rows above (one is blank) column U would have a header of "Variable 1" and rows 1 and 5 would have a 1 in them. Column V would have a heading that says "Another Variable" and rows 1 and 5 would have a 1 in that column. Column W would have a heading that says "Here is yet another one" and rows 1 and 2 would have a 1. Etc. To make it more interesting, new variables could be added in the future so I'd need the macro to create headers based on what's in the file's list of variables, not a finite list that I know in advance.
There could be as many as 10 variables, separated by commas, in a cell of the original data. After adding the new columns, column R can be deleted. I can do that in the macro - just not the part that requires intelligence.
Listing: Variable1, Another Variable, Here is yet another one, Variable12, Last one.
Listing: Variable7, Here is yet another one, Variable12
Listing: Variable12
Listing: Variable1, Another Variable, New variable nobody ever saw before, Variable1
Some (most) rows have nothing in column R. "Listing:" is sort of a heading and it only appears if there are variables. If there are no variables the cell is completely empty. When there's more than one variable, they're separated by commas. I know how to split up the data by using the commas. What I can't figure out is how to make each variable that's in the cell populate it's own column starting in row U. So with the examples of the 5 rows above (one is blank) column U would have a header of "Variable 1" and rows 1 and 5 would have a 1 in them. Column V would have a heading that says "Another Variable" and rows 1 and 5 would have a 1 in that column. Column W would have a heading that says "Here is yet another one" and rows 1 and 2 would have a 1. Etc. To make it more interesting, new variables could be added in the future so I'd need the macro to create headers based on what's in the file's list of variables, not a finite list that I know in advance.
There could be as many as 10 variables, separated by commas, in a cell of the original data. After adding the new columns, column R can be deleted. I can do that in the macro - just not the part that requires intelligence.