0

I have an Excel spreadsheet with customer details where the facility name, contact name, address and email is in separate rows but I need them to be in one row in separate columns.

To cut and paste takes forever, and if I insert = to cell, once I delete the obsolete rows the content obviously shows

Evelyn Page Retirement Village                      
30 Ambassador Glade                         
Lynn Charlton                               
lynn.charlton@evelynpage.co.nz      

compared to

Evelyn Page Retirement Village  30 Ambassador Glade Lynn Charlton lynn.charlton@evelynpage.co.nz
2

If you only have one set of values (like in your question), then as Christofer Weber states in the comments, you can use the Transpose option for pasting, as per the guide which Christofer linked to.

However, if you have multiple records presented in this way and want to transpose them into a table, it's a little more difficult. So for example you have:

Source

And you want:

Target

You can enter the formula:

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

into any cell, then drag it across and down as required to populate your new table (in the example above, drag the formula across the 3 cells adjacent to the right and then down 2 rows to leave a table with 4 columns and 3 rows). If you had a different number of values in each record, simple switch out the 4s in the formula for the appropriate number of values per record.

You could also achieve this with some VBA, or by using 3rd party extensions for Excel, many of which provide an easier GUI-driven method to achieve this same result. I wouldn't like to recommend any specific ones as I haven't used them myself though.

  • Thanks jcbermu for inserting my images inline - hopefully I'll have enough rep to do that for myself before too long! – 3N1GM4 Nov 21 '16 at 9:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.