| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose multiple columns, functions
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
On Mon, 14 Apr 2008 11:08:00 -0700, Nancy
wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
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 On Apr 14, 7:08*pm, Nancy wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
how would i seperate those into seperate cells?
"Pete_UK" wrote: 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 On Apr 14, 7:08 pm, Nancy wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
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? "Pete_UK" wrote: 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 On Apr 14, 7:08 pm, Nancy wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
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--- -- RyGuy "Nancy" wrote: 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? "Pete_UK" wrote: 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 On Apr 14, 7:08 pm, Nancy wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple columns, functions
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 On Mon, 14 Apr 2008 11:54:03 -0700, Nancy wrote: how would i seperate those into seperate cells? "Pete_UK" wrote: 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 On Apr 14, 7:08 pm, Nancy wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need a macro to transpose multiple columns A1-Z1, A2-X2 etc | Excel Discussion (Misc queries) | |||
| Multiple Functions across Two Columns | Excel Worksheet Functions | |||
| using functions to compare multiple columns for mismatch of cells | Excel Worksheet Functions | |||
| Is it possible to transpose 2 columns of data into 1 row? | Excel Discussion (Misc queries) | |||
| how do I transpose columns and rows | Excel Discussion (Misc queries) | |||
Linear Mode
