Combining non adjacent columns into a list

[Scoonidge]

Hi All

This is my first ever post so please bear with me.

Wondering if anyone can point me in the right direction. I have 5 columns of survey data with each row being a survey response. The 5 columns represent a multiple choice and multiple answer question that I want to combine into one list.

I have used the concatenate function to combine the 5 columns together but obviously because it is multiple answer there are some cells in the column that have more than one answer. I have tried using the text to columns button but that just puts the formula into the cell not the actual text

On some of the other questions I have used an indexing system which works perfectly but those are for columns next to each other. The columns i am wanting to combine are not.

Any ideas, solutions would be greatly appreciated.

 

[Dannyh1]

Hi,

would it be possilbe to post an example?

 

[Scoonidge]

Hi

Data looks like currently looks like this:
Col 1 Col 2 Col3 Col4 Col 5
Resp 1 Fruit Apples Pears Banana Sweets
Resp 2 Crisps Walkers

 

[Scoonidge]

Sorry will get the hang of this eventually

Data looks like currently looks like this:
Col 1 Col 2 Col3 Col4 Col 5
Resp 1: Fruit Apples Pears Banana Sweets
Resp 2: Crisps Walkers Wotsits Quavers Fruit

I would like to combine Column 1 and 5 into a list that looks similar to this
Fruit
Sweets
Crisps
Fruit

I have used the concatenate function so the lists currently looks like this:
FruitsSweets
CrispsFruit

When i try to then split this up using the text to columns it just puts the formula in the blank cell.

Hope this makes things a little clearer.

 

[Dannyh1]

Hi

For this is example, it looks like concatenate would work.

Can you post an example of the part where you say:

"I have used the concatenate function to combine the 5 columns together but obviously because it is multiple answer there are some cells in the column that have more than one answer."

 

[Scoonidge]

Hi

Yes the concatenate bit does work in that it puts the contents of cell a1 (in this case Fruit) and the contents of cell a5 (in this case Sweets) into 1 cell so it reads FruitSweets in for example cell a6.

The bit i seem to be having difficulty with is getting them to all appear in a list so they are all under one another. I thought the text to column wizard would easily fix this but when i try and do this it just puts the concatenate formula in the cell and not the response.

Hope this is a bit clearer

 

[Dannyh1]

Hi,

this solution a bit messy but if don't get another try:

Create another sheet (Sheet2)

Assuming that:

"Fruit" (from response 1) is in Sheet1, cell B2
"Sweets" (from response 1) is in Sheet1, cell F2

On Sheet2:

cell A1 = 0
cell A2 = 0
cell A3 = A1+1 Copy this formula down

cell B1 =INDIRECT("'Sheet1'!B"&2+A1)

Copy this formula down.

 

[John Davis]

Sorry will get the hang of this eventually

Data looks like currently looks like this:
Col 1 Col 2 Col3 Col4 Col 5
Resp 1: Fruit Apples Pears Banana Sweets
Resp 2: Crisps Walkers Wotsits Quavers Fruit

I would like to combine Column 1 and 5 into a list that looks similar to this
Fruit
Sweets
Crisps
Fruit

I have used the concatenate function so the lists currently looks like this:
FruitsSweets
CrispsFruit

When i try to then split this up using the text to columns it just puts the formula in the blank cell.

Hope this makes things a little clearer.

Maybe too much trouble, but does this help?

With a helper column (H) and List in Column (G)

H1 enter 0
H2 enter 0
H3 =H2+1
H4 =H3
Copy H3 & H4 and paste by pairs as far down as needed

G1 =OFFSET($A$1,H1,0)
G2 =OFFSET($E$1,H2,0)

Copy G1 & G2 and paste down by pairs as far as required.

G          H
Fruit	0
Sweets	0
Crisps	1
Fruit	1
total 	2
total	2
july	3
dec	3
	4
	4
	5
	5
	6
	6
	7
	7