CODEWITHSUNDEEP
excelexcel-formula
Elnaz Sarraf
Elnaz Sarraf

Reputation: 9

Convert addresses on separate lines into one row each

I have an Excel sheet that has several rows but I need to convert them to columns. So each row is actually one column. I have this

Name
Address
City
Info
Website
"Empty Space"


Name
Address
City
Info
Website
"Empty Space"

but I want:

Name   Address   City   Info   Website
Name   Address   City   Info   Website
Name   Address   City   Info   Website

My document has 22,467 rows and I also don't know how to create and run a macro for this. I really appreciate your help. I have already tried transpose but it gives me error. I am using Microsoft Office 2008 on Mac.

Upvotes: 0

Views: 1721

Answers (1)

pnuts
pnuts

Reputation: 59485

Assuming each block is 5 rows of Name/Address/City/Info/Website and each block is separated by three blank rows and the first Name is in A1, then in B1, copied across to F1: 

=OFFSET($A1,MOD(ROW(),9)/9+COLUMN()-2,0)

In G1: 

=MOD(ROW(),9)

Copy B1:G1 down to suit. Then select and copy entire sheet and Paste Special, Values over the top.

Filter ColumnG to select all but 0 and delete all visible rows and delete ColumnA.

Upvotes: 1

Related Questions