Oct 14 2021 03:04 AM - edited Oct 14 2021 03:16 AM
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!!
Oct 14 2021 03:47 AM
SolutionSee the attached workbook.
Oct 15 2021 02:34 AM
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!!
Oct 17 2021 08:32 AM
@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!!
Oct 17 2021 01:09 PM
The formula in the attachment to my previous reply already skips empty cells. What exactly is the problem?
Oct 18 2021 04:28 PM
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!
Oct 19 2021 03:33 AM
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.
Oct 19 2021 05:43 AM
@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.
Oct 21 2021 07:09 AM
@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
Oct 23 2021 10:04 AM
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
Oct 23 2021 04:18 PM - edited Oct 24 2021 09:15 AM
sorry, but still the Excel close down because of the load...
Help!! PLZ
Oct 14 2021 03:47 AM
Solution