Forum Discussion

Eladnoam2's avatar
Eladnoam2
Copper Contributor
Oct 14, 2021
Solved

merging cells

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 ...
excel
Eladnoam2's avatar
Eladnoam2
Copper Contributor
Oct 17, 2021

HansVogelaar 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!!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 17, 2021

    Eladnoam2 

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

    • Eladnoam2's avatar
      Eladnoam2
      Copper Contributor
      Oct 18, 2021

      hi HansVogelaar 

      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!

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Oct 19, 2021

        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 HansVogelaar , 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.