Moving Data from Rows to Columns

[Josue Preciado]

Hello everyone! I have a set of data that contains addresses for
thousands of vendors. An example is listed below:

COLUMN 1 COLUMN 2 COLUMN 3
Vendor #1
1111 MULLBERRY CIRCLE EAST
CHANHASSEN MN
EMPTY CELL
EMPTY CELL
EMPTY CELL
Vendor #2
1111 HIDDEN COURT
CHANHASSEN MN 55317

As you can see, each vendor's information is on 3 rows (sometimes 4 or 5
if the address is long), and is then followed by 3 empty cells to
seperate each vendor. All of this is one column. What I need to do, is
take each vendor's address, and move it from being verticle (3-5 rows)
to horizontal (3-5 columns). So the end data would look like:

COLUMN 1 COLUMN 2 COLUMN 3
Vendor #1 1111 MULLBERRY CIRCLE EAST CHANHASSEN MN
Vendor #2 1111 HIDDEN COURT CHANHASSEN MN 55317

Any thoughts or help?

*** Sent via Developersdex http://www.developersdex.com ***

 

[Alok]

Hi,
Your question can more simply be handled by a formula
Assuming your data is lying in ColumnA of Sheet1
use the following formulas. In..
Cell A1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6,0,1,1)
Cell B1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6+1,0,1,1)
Cell C1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6+2,0,1,1)
Cell D1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6+3,0,1,1)
Cell E1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6+4,0,1,1)
Cell F1 of Sheet2 : =OFFSET(Sheet1!$A$1,(ROW()-1)*6+5,0,1,1)

Then copy and paste A1:F1 in as many rows as you need to.