0

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(....)

Flethuseo
  • 999

3 Answers3

0

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))

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

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

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

bertieb
  • 7,543
Helen
  • 1
0

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
nutsch
  • 1,971
0

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:

SU495953 example

pnuts
  • 6,242