Transposing a set of records

Question

I need to transpose records from a table, but by groups like shown in the example below. I need to do this for many records identified by a row number at the beginning of the table.

Need to transpose From:

1   LastModified$Time   2011-05-10 12:57:03.0
1   Count1  0
1   Count2  58
1   LastModified$UserName   mbeardsell
2   LastModified$Time   2010-03-31 21:45:53.0
2   Count1  0
2   Count2  0
2   LastModified$UserName   MBeardsell
3   LastModified$Time   2006-12-13 12:55:47.0
3   Count1  1
3   Count2  0
3   LastModified$UserName   markhall

to:

    LastModified$Time   Count1  Count2  LastModified$UserName
1   2011-05-10 12:57:03.0   0   58  mbeardsell
2   2010-03-31 21:45:53.0   0   0   MBeardsell
3   2006-12-13 12:55:47.0   1   0   markhall

P.S. I am not understanding the answers I am getting. I am going to rephrase my question to be more clear. I want to be able to do this:

If I have the values:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

I want to transpose them by sets of five as follows:

1   2   3   4   5
6   7   8   9   10
11  12  13  14  15

I have to do this for many many records, and I can't really stop and go through every five cells and type =Transpose(....)

Answer 1

Easiest will be to:

• Run autofilter on LastModified$Time
• in the second column of the results, reference the Count 1 cell and copy down
• in the third column of the results, reference the Count 2 cell and copy down
• in the fourth column of the results, reference the UserName cell and copy down
• clear the filter, copy paste values
• replace LastModified$Time by Nothing (as in Nothing)
• replace LastModified$UserName by Nothing (as in Nothing)
• replace Count1 by Nothing (as in Nothing)
• replace Count2 by Nothing (as in Nothing)
• Delete blank lines (with an autofilter)
• add your titles
• You're done

Answer 2

Similar to @nutsch’s answer, but (possibly) with less risk of error (no formulae involved) and in case your data starts as just two columns would be to:

• In Data, Text to Columns (delimited by space) [if necessary to parse one column].
• Copy ColumnA:D to ColumnE and to I and to ,M.
• Delete (shift up) E1:P1 then I1:P1 then M1:P1.
• Add Row1 and drag text from ColumnB to column labels to suit.
• Filter all, with ColumnB deselecting LastModified$Time.
• Delete Row3 to end.
• Except for ColumnA and ColumnC, delete columns without labels.
• Remove filter, format to fit column width.

Above (roughly) described visually, but considering revised element of question:

Answer 3

I found this solution the easiest:

https://www.extendoffice.com/documents/excel/3360-excel-transpose-every-5-rows.html

Ensure your single data column is in column A, starting in A1

1. Insert this formula in a new cell nearby:

=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))

2. Just change both occurrences of number "5" to match the size of your group set. (In this case - the revised presentation - it is 5)

3. Drag the formula across to the right to also match the size of your group set. (In this case, drag across 5 columns)

4. Drag the formula down until your data stops presenting (you'll get "0"'s)