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!

[Gary''s Student]

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.
--
Gary's Student


"EMCUE" wrote:

>
> 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!
>
>
> --
> EMCUE
> ------------------------------------------------------------------------
> EMCUE's Profile: http://www.excelforum.com/member.php...o&userid=32945
> View this thread: http://www.excelforum.com/showthread...hreadid=531115
>
>

[EMCUE]

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

[Gary''s Student]

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.
--
Gary's Student


"EMCUE" wrote:

>
> how would i skip blank cells so that they aren't transposed
>
>
> --
> EMCUE
> ------------------------------------------------------------------------
> EMCUE's Profile: http://www.excelforum.com/member.php...o&userid=32945
> View this thread: http://www.excelforum.com/showthread...hreadid=531115
>
>