text to columns-- to certain columns depending on the text value

[MoniNalu]

I have downloaded survey data from an online survey that comes as comma separated values in a cell. I need to split the cell values into columns, but I want the values to go into certain columns depending on the value.

My example (see attached): survey takers can select up to 6 items from a list. Because they each select different items, if I use text-to-columns, the values are moved to columns, but the columns become first selection, second selection, etc. Instead, I want each column to be a certain item/value. So if a person did not select the first value, there is nothing in the first column for that person. And so on.

I have attached a file with an example of the raw data and the desired output (note: I am not wedded to the column headings).

I believe others must have already asked about this, but unfortunately I cannot find the answer. Thank you for any help you can give.

--Moni

[mike7952]

Heres a macro that will do it.

survey_responses_SingleColumnRawData.xlsm

[MoniNalu]

You are awesome! Thank you so very much (mahalo a nui loa, as we say here in the islands).

Moni

[mike7952]

(A`ole pilikia) or how we say in Keys Youre Welcome No problem