Multiple columns to a single column

[RhettWS]

Hello and thanks in advance,

I've been provided a data set that I need to rearrange for statistical analysis. Columns represent responses from a single person to a questionnaire. Rows represent each question. I've deleted the questions and redacted any names, etc.. There are 9 columns in my attachment although the full data set from one file has approximately 50 columns.

For statistical purposes I need to convert all the columns to a single column of data. I basically need to cut the data column B and paste it directly under column A, and so on. I can do this one column at a time but I have approximately 50 columns in each file and I have about 5 files. Hopefully you can see my need for some easier way to do this and if I attached the file incorrectly feel free to verbally flog me.

Thank you in advance. ....... Rhett

[Pete_UK]

Do you want to retain the blanks that occur throughout the data?

Pete

[Pete_UK]

Put this formula in cell K2:

=INDEX(A:I,MOD(ROWS($1:1)-1,41)+2,INT((ROWS($1:1)-1)/41)+1)

Apply a Custom Format to that cell of:

General;;

(this will turn zeroes to blanks) then copy the formula down as far as you need to.

Hope this helps.

Pete

[RhettWS]

Pete,

The blanks must be retained. Those blanks are equivalent to NA (not applicable). I'll try that formula you suggested and I'll update.

Regarding the blanks I'll ultimately put something in that space. Once I get it all in one column I can sort and then stick a 0 or something there. The data are responses to a question where the responses are a Likert scale.

Thanks for your help. Rhett

[Pete_UK]

I suggest you do not use a zero to fill those blanks, as that will just show as blank with the formatting I suggested - use "NA" or "x" or -1 ...

Pete

[RhettWS]

Pete, it works like a charm and the zeros are what I wanted anyway for now. Sweeeeet!!!

Thanks again ............. Rhett