Transpose data

  • Thread starter Thread starter Nenagh
  • Start date Start date
N

Nenagh

Hi

I have a database in one column but each address field is in a different
row, so

Co
Add1
Add2
Sub

and then a repeat of the same for the next record. I want to have each
address field in it's own column. I know i can copy and paste special
transpose but this is a line by line task. Wondering if there was someting
else i could do

Cheers
 
If data is consistently sets of 4 cells as your example.

In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($A:B)-1)

Copy across to E1

Select B1:E1 and drag/copy down until zeros show up.

Select columns B:E and copy>paste special>values>ok>esc

Delete original column A


Gord Dibben MS Excel MVP
 
Back
Top