Transpose multiple columns, functions

[Nancy]

PLEASE HELP!!! I'm GOING CRAZY!
i have 3 seperate columns
EX:
NAME CITY STATE
Nancy Sacramento CA
Danny Miami FL
Lucy St. Paul MN
Shorty Hickory NC

i want to transpose all 3 columns into 1 row (seprate cells)
EX:
Nancy Sacramento CA Danny Miami FL Lucy St. Paul MN Shorty Hickory NC
Is this even possible? i know you can do 1 column but what about 3?
IS there a formula i can use to pull the information?

EX:
If i transpose just the 1st column and insert two blank columns between the
names
Nancy Danny Lucy
Shorty
is there a function i can use to pull the information above and insert it in
the blank cells?

EX:
i tried the IF(Nancy=a1:a5,b1:b5,Error) and it worked, but only with Nancy

i guess what i'm trying to say is, find the name, pull city and state of
that person and paste it in the blank cells....

Please help, i'm so confused i don't even know what i'm talking about anymore.

 

[Lars-Åke Aspelin]

PLEASE HELP!!! I'm GOING CRAZY!
i have 3 seperate columns
EX:
NAME CITY STATE
Nancy Sacramento CA
Danny Miami FL
Lucy St. Paul MN
Shorty Hickory NC

i want to transpose all 3 columns into 1 row (seprate cells)
EX:
Nancy Sacramento CA Danny Miami FL Lucy St. Paul MN Shorty Hickory NC
Is this even possible? i know you can do 1 column but what about 3?
IS there a formula i can use to pull the information?

EX:
If i transpose just the 1st column and insert two blank columns between the
names
Nancy Danny Lucy
Shorty
is there a function i can use to pull the information above and insert it in
the blank cells?

EX:
i tried the IF(Nancy=a1:a5,b1:b5,Error) and it worked, but only with Nancy

i guess what i'm trying to say is, find the name, pull city and state of
that person and paste it in the blank cells....

Please help, i'm so confused i don't even know what i'm talking about anymore.

Try this:

If your table starts on row 2, i.e. NAME is in A2, CITY in B2 and
STATE is in C2, then put the following formula in cell A1

=OFFSET($A3;(COLUMN()-1)/3;MOD(COLUMN()-1;3))

Copy the formula as far to the right as you need.
Row 1 will den be filled with data from your table.

Hope this helps / Lars-Åke

 

[Pete_UK]

I'm not really sure what you want, but try this in D1:

=$A1 & " " & $B1 & " " & $C1

Copy this into E1:G1, but change the row number each time, i.e.:

E1: =$A2 & " " & $B2 & " " & $C2
F1: =$A3 & " " & $B3 & " " & $C3
G1: =$A4 & " " & $B4 & " " & $C4

Hope this helps.

Pete

 

[Nancy]

how would i seperate those into seperate cells?

I'm not really sure what you want, but try this in D1:

=$A1 & " " & $B1 & " " & $C1

Copy this into E1:G1, but change the row number each time, i.e.:

E1: =$A2 & " " & $B2 & " " & $C2
F1: =$A3 & " " & $B3 & " " & $C3
G1: =$A4 & " " & $B4 & " " & $C4

Hope this helps.

Pete

 

[Nancy]

okay so now i have name, city, stat in one cell.

nancy sacramento ca
danny maiam fl

after i transpose it

cell 1 cell 2
nancy sacramento ca danny miami fl

how would i make that 6 columns instead of 2?

 

[ryguy7272]

I don't really understand the point of this exercise, but I suppose you could
do the following:
Copy > Right-Click > Past Special > Values > Data > Text to Columns >
Delimited > Tab or Space or Comma > Finish

Does that do what you want to do?

Regards,
Ryan---

 

[Gord Dibben]

Nancy

Do not ignore Lars-Ake's formula solution which works as you want.

Just make sure you change his semi-colons to commas.


Gord Dibben MS Excel MVP