'Unconcatenate' several columns

[Glayva]

Pete_UK very kindly helped me with a this query this morning - http://www.excelforum.com/excel-form...their-own.html but I have since realised my data gets more complicated than that.

As per the updated attachment, I not only have the potential for more than two variations in a cell, I also have more than one column like this.

So I had thought I could just copy the column to the end of the spreadsheet (as the number of potential variables is not fixed) and use the data> text to columns trick, using '>' as the delimiter. However, it's impossible to have both columns at the end of the sheet.

It may be something that isn't solvable as code as it seems to be an issue of structure too. Basically what I want to be able to do is segment the data in several ways such as:

- x number of people picked blue as the first colour as well as picking cup as the last object
- y number of people chose both green and flower at some point.

Ta

Glayva

[Martijnvc]

If you have a known number of variations you could use =SEARCH("COLOUR";A1;1). If true, one named the colour otherwise not.

Other way is to use text-to-columns with ">" as delimiter. You can indicate where it has to go so column does not need to be the last column. You can click "Next" instead of "Finish" when using the text-to-columns, which then somewhere gives you an option "destination". Just indicate what is the most topleft cell where you want to copy the data to and you're done?

Is that what you're after?

[Marcol]

Did you look at my suggestion in your earlier post? Post #4, drag it Across as far as you need, and then Down.

Extracting two variables from one cell and placing them into columns of their own

Please Login or Register  to view this content.

[Glayva]

Thanks both - both of your suggestions work when I have one column like this, but there is still the problem of the second column getting in the way. As I say - I think this is a structure thing more than anything so will have a think.

Thanks

[Martijnvc]

What do you exactly mean with "the second column getting in the way"? You can apply the text-to-columns technique regardless of where your first and second column are placed, and regardless of where you have space to copy the data to as long as you have some empty columns left. You can paste them in XYZ if you want to..

I think before putting much effort in restructuring your complete Excel file you should give either solution above another go.

[Glayva]

I have tried both options Matijnvc. The text to columns does not work as I have to know how many variables will be in the cells, so do not know how to many columns to keep free. If I choose 8 columns and add the next full column to be broken down in the 9th column, for example, but there are 9 variables split by the delimiters, the text-to-columns action will run into the 9th column.

As I don't have a known number of variations the other suggestion will not work either.

Thanks

Glayva

[Marcol]

The problem is, as you are aware, is with the layout of your data.
You need to know how many columns to leave before you begin the next set of results, no?
This CSE array will tell you that, confirm with Ctrl+Shift+Enter not just Enter

Please Login or Register  to view this content.

Or if you want both result tables to be the same width, again CSE entered.

Please Login or Register  to view this content.

If you get this info first, and layout your sheet accordingly, any of the solutions offered can then be used.

Personally, I would re-think the layout.