| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate data and put them into one line
I have 2 columns that have the records like
Column A Column B Bert Dallas Bert Texas Bert Male Mary Houston Mary Texas Mary Female and I would like to combine them into one row Column A Column B Column C Column D Bert Dallas Texas Male Mary Houston Texas Female I've tried to do it using if statement, But I don't know have to make them into one line. Is there any excel function that allow me to do it? If not, what should i do? Please advise. Thank you for the help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Separate data and put them into one line
Make sure your columns have a header then
Go to Menu select DataFilterAuto Filter "iwins" wrote: I have 2 columns that have the records like Column A Column B Bert Dallas Bert Texas Bert Male Mary Houston Mary Texas Mary Female and I would like to combine them into one row Column A Column B Column C Column D Bert Dallas Texas Male Mary Houston Texas Female I've tried to do it using if statement, But I don't know have to make them into one line. Is there any excel function that allow me to do it? If not, what should i do? Please advise. Thank you for the help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Separate data and put them into one line
If you preferred automatic method then try this
Let's say you have your data in A2:B7 Bert in A10 Mary in A11 B10 =IF(ISERR(SMALL(IF($A$2:$A$7=$A10,ROW(INDIRECT("1: "&ROWS($B$2:$B$7)))),COLUMNS($A:A))),"",INDEX($B$2 :$B$7,SMALL(IF($A$2:$A$7=$A10,ROW(INDIRECT("1:"&RO WS($B$2:$B$7)))),COLUMNS($A:A)))) It's an array formula. You have to commit ctrlshiftenter (not just enter) Copy across to the right and down "iwins" wrote: I have 2 columns that have the records like Column A Column B Bert Dallas Bert Texas Bert Male Mary Houston Mary Texas Mary Female and I would like to combine them into one row Column A Column B Column C Column D Bert Dallas Texas Male Mary Houston Texas Female I've tried to do it using if statement, But I don't know have to make them into one line. Is there any excel function that allow me to do it? If not, what should i do? Please advise. Thank you for the help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Separate data and put them into one line
This works. Thank you.
"Teethless mama" wrote: If you preferred automatic method then try this Let's say you have your data in A2:B7 Bert in A10 Mary in A11 B10 =IF(ISERR(SMALL(IF($A$2:$A$7=$A10,ROW(INDIRECT("1: "&ROWS($B$2:$B$7)))),COLUMNS($A:A))),"",INDEX($B$2 :$B$7,SMALL(IF($A$2:$A$7=$A10,ROW(INDIRECT("1:"&RO WS($B$2:$B$7)))),COLUMNS($A:A)))) It's an array formula. You have to commit ctrlshiftenter (not just enter) Copy across to the right and down "iwins" wrote: I have 2 columns that have the records like Column A Column B Bert Dallas Bert Texas Bert Male Mary Houston Mary Texas Mary Female and I would like to combine them into one row Column A Column B Column C Column D Bert Dallas Texas Male Mary Houston Texas Female I've tried to do it using if statement, But I don't know have to make them into one line. Is there any excel function that allow me to do it? If not, what should i do? Please advise. Thank you for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
