Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Column A I have a number. In Column B I have a name as well as data that should go in columns C and D.

How can I turn this:

what I have

into this:

what I need


share|improve this question
Is First name supposed to be Abbruzzese Gabriele? Also do you mean row or column? – assylias Mar 19 '12 at 12:00
Can you provide your data more clear and understandable way? – Siva Charan Mar 19 '12 at 12:23
I hope now's better – Pitto Mar 19 '12 at 17:22
up vote 1 down vote accepted

Assume: Every 3 rows of old record = 1 row of new record

  1. Enter into C1: =B2 and copy down to C2:C999
  2. Enter into D1: =B3 and copy down to D2:D999
  3. Enter into E1: =MOD(ROW(E1) ,3) = 1 and copy down to E2:E999
  4. Copy column C:E
  5. Select C1 > Edit > Paste Special > Value ( Alt-E > S > V > Enter )
  6. Turn on AutoFilter: Data > Filter > AutoFilter ( Alt-D > F > F )
  7. Select FALSE in column E auto filter drop-down list
  8. Select row 2:999 > Right click > Delete
  9. Delete dummy column E:E
  10. Turn off auto filter: Data > Filter > AutoFilter ( Alt-D > F > F )
share|improve this answer
That's pure binary-minded information :D You win. – Pitto Mar 20 '12 at 10:21
I found another answer which is also good. – wilson Mar 21 '12 at 2:12

copy - paste special - transpose

share|improve this answer
it works but I shoud do it manually for a lot of rows... – Pitto Mar 19 '12 at 17:22
record a macro? Is it always the same spacing? – Raystafarian Mar 19 '12 at 18:39
Might not fit the use case which the OP asked about, but it certainly fits the title of this question, and as such was verys useful to me. – MvG Jun 1 '13 at 19:20

Your Answer


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.