Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.