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!

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!

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!

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!