Move every 7 columns into new row in Excel [duplicate]

Question

I have a very large data set in Excel; all of the outcome scores are in a single column. I need these scores to be separated into rows so that every set of 7 scores (i.e., scores 2-8, 9-16, etc) are in their own rows (each row will then be an individual, and each column will be a score at a different time with 7 times in all).

How can I do this in Excel?

Answer 1

As you have not indicate what version of Excel you are using, nor have you given an example of your data. So any "right answer" will be a wild ass guess.

If you have Excel 2010, and your scores are seperated by comma's (i.e. 23-42, 11-17, 1-5) then use Text to columns (Data > Data Tools > Text to Columns; to seperate the scores in to indivual columns.

Then insert a row after every 7 seperate rows and use concatinate to join the together in groups of 7

=B1&C1&D1&E1&F1&G1&H1

If this is not the "right answer" give us an example of the data and tell us what version of excel you are using.

Answer 2

Like the other posters here, I made some assumptions about your data. See below. I assume each score goes with a name, at a time, and there are 7 times per name. So, you can first sort the data by name. Then, assuming the "times" aren't really relevant, you can add a "TimeNumber" column. Finally, build a pivot table.

Answer 3

The easiest way to do this is with a VBA macro:

Sub Every7()
    Dim i As Integer, j As Integer, cl As Range
    Dim myarray(100, 6) As Integer 'I don't know what your data is.  Mine is integer data
    'Change 100 to however many rows you have in your original data, divided by seven, round up
    'remember arrays start at zero, so 6 really is 7

    If MsgBox("Is your entire data selected?", vbYesNo, "Data selected?") <> vbYes Then
        MsgBox ("First select all your data")
    End If

    'Read data into array
    For Each cl In Selection.Cells
        Debug.Print cl.Value
        myarray(i, j) = cl.Value
        If j = 6 Then
            i = i + 1
            j = 0
        Else
            j = j + 1
        End If
    Next

    'Now paste the array for your data into a new worksheet
    Worksheets.Add
    Range(Cells(1, 1), Cells(101, 7)) = myarray
End Sub

This takes this:

and transforms it to this: