Transpose Function

[EMCUE]

Having another problem.

How can I set up a function to transpose vertically placed text data
horizontally automatically without doing so manually. I have a very
long list of contact information that is formatted as five vertical
cells per contact a blank cell and then the pattern continues. Any
help? I am desperate!

 

[Guest]

Lets say you have data in column A starting in A1 that looks like:

Name 1
Address 1
City 1
State 1
zip1

Name 2
Address 2
City 2
State 2
zip2

Name 3
Address 3
City 3
State 3
zip3

In B1 enter:
=INDIRECT("A" & ROW()*6-7+COLUMN())
copy this from B1 to C1 thru F1
then copy B1 thru F1 downward as far as you need.
You will seein B1 thru F3:

Name 1 Address 1 City 1 State 1 zip1
Name 2 Address 2 City 2 State 2 zip2
Name 3 Address 3 City 3 State 3 zip3

Each block of five items in the single column transposed into a separate row.

 

[EMCUE]

how would i skip blank cells so that they aren't transposed

 

[Guest]

The function already skips the single blank cell between the sets of 5, but
as coded it needs a group of 5, a blank, another group of 5, another blank,
etc.


If there occasional groups of four or three, then you would need a macro to
automatically synch up on the blanks.