converting vertical data list to horizontal data list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjb - 14 Jul 2006 22:02 GMT

OK here's the issue as best I can explain it:

In column A there are multiples names all associated with one name in column
B. For example:

Col. A Col.B
Jimmy Doe John Doe
Jackie Doe John Doe
Joey Doe John Doe
Jane Doe James Doe
June Doe James Doe

What I need is all of the names in column A to be listed in rows rather than
columns so for example:

Col. A Col. B Col. C
Col. D
Jimmy Doe John Doe Jackie Doe John Doe

I know that what I want to do is systematically possible, I just hope I've
explained it well enough. I'm comfortable working with VBA code, just not
quite sure how to set it up.

Reply to this Message

Esther - 15 Jul 2006 00:20 GMT

I have done the reverse of this and it was a royal pain. Converted a matrix
into a format for uploading to a database earlier this year. It takes a
series of multi-leveled IF statements, and you need to be comfortable with
that. Let me know if you want more detail and I can send you my formulas.
-Esther

> OK here's the issue as best I can explain it:
>
[quoted text clipped - 18 lines]
> explained it well enough. I'm comfortable working with VBA code, just not
> quite sure how to set it up.

Reply to this Message

Ragdyer - 15 Jul 2006 02:17 GMT

From your example, if I understand it correctly, it looks like you want to
copy across columns, along a row in this manner:

A1, B1, A2, B2, A3, B3, ... etc.

If that be the case, enter this formula *anywhere* and copy across as
needed:

=INDEX($A:$B,COLUMNS($A:B)/2,MOD(COLUMNS($A:A)-1,2)+1)

If I guessed wrong, post back with the order of the cells that you would
like.

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> OK here's the issue as best I can explain it:
>
[quoted text clipped - 21 lines]
> explained it well enough. I'm comfortable working with VBA code, just not
> quite sure how to set it up.

Reply to this Message