merging cells

[Eladnoam2]

i have a long table like this:

phone	animal	food
123	dog
123	cat
123	fish
123		pizza
123
123		hot dog
456
456	cat
789	fish	pizza
789		hot dog

and i want a result like this:

phone	animal	food
123	dog,cat,fish	pizza,hot dog
456	cat
789	fish	pizza,hot dog

ty!!

[#2 · Accepted solution]

@Eladnoam2 

See the attached workbook.

[#3]

Wonderful TY Hans!!

[#4]

hi,

i dont want to get a duplicate value, fo example:

i have a long table like this:

phone	animal	food
123	dog
123	dog
123	fish
123		pizza
123		hot dog
123		pizza

and i want a result like this:

phone	animal	food
123	dog,fish	pizza,hot dog

and not like this:

phone	animal	food
123	dog,dog,fish	pizza,hot dog,pizza

ty so much!!

[#5]

@Eladnoam2 

New version attached.

[#6]

u r genius. TY!! Hans Vogelaar

[#7]

@Hans Vogelaar ty!

hope that its the last one...

i have a table of 950,000 rows.

i want that the function play only on non empty cell. I thought...

=IF($A$2:$A$11<>"",TEXTJOIN(",",TRUE,UNIQUE(IF($C$2:$C$11=$F2,$A$2:$A$11&"",""))),"")

but its not work...

any idea? TY!!

[#8]

@Eladnoam2 

The formula in the attachment to my previous reply already skips empty cells. What exactly is the problem?

[#9]

hi @Hans Vogelaar 

i have many rows (950,000 rows). and when i put thw formula that u made, the Excel course from the load.

many of my cells (for example, the Animal, and not the Number) are empty. there for i want that the formula get action only in non-empty cells.

i tried somthing in the file below (red font), but its worng. (i thought to use in if statment...)

ty so much!

[#10]

@Eladnoam2 

I'm sorry, I really don't understand what's wrong with the solution that I proposed. It does not have the problem that your formula has.

[#11]

@Eladnoam2 You wrote ".....when I put the formula that u made, the Excel course from the load." Did you perhaps mean to say that the formula based solution offered by @Hans Vogelaar , applied to 950 thousand rows caused Excel to become very slow or that it is no longer responding? 

If so, perhaps you want to consider a Power Query solution (see attached).  PQ deals with large data sets very well. Though, I suspect that your real data set is not about three columns of telephone numbers, foods and animals. So, you can't just apply the query in the attached file to anything not looking exactly the same as your sample data.  Perhaps best that you show us what you are really dealing with. Then we don't need to guess what your true intentions are.

[#12]

@Riny_van_Eekelen, yes, thats what I meant, even worse, the Excel close down because of the load.

I attached file, my file is much bigger, i have 7 columns, with much more data.

in the file, i put the formula of @Hans Vogelaar

only at the first 100 rows, and its take a minute to end the action.

thank you so much!

soory about my bad English

[#13]

@Eladnoam2 

If slightly modify @Hans Vogelaar formula

=IFERROR( TEXTJOIN(",",1, FILTER(Table1[animal], NOT(ISBLANK(Table1[animal])) * (Table1[phone] = F2) ) ), "")

perhaps it'll be bit faster

[#14]

sorry, but still the Excel close down because of the load...
Help!! PLZ 

@Sergei Baklan

@Hans Vogelaar

@Riny_van_Eekelen