Forum Discussion

sandipgumtya's avatar
sandipgumtya
Copper Contributor
May 27, 2020

Unique items in a List

Hi all, I need help on this minor issue. I have a list and want to extract unique values from it. I have tried with two formula but the end of both the list displays zero which I am not able to get ...
Excel for web
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 27, 2020

sandipgumtya Try using the new UNIQUE function. Should be available in Excel for the web.

=UNIQUE(D2:D23)

 

sandipgumtya's avatar
sandipgumtya
Copper Contributor
May 27, 2020

Riny_van_Eekelen 

Hi ,

Though I am using office 365, these new functions are not yet available to me. 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 27, 2020

    sandipgumtya Introduced a named formula that creates a dynamic range from D2, down to the last cell in column D. changed both formulae you provided. Now, you no longer have the zeros, even when the list of unique items is shorter than the number of rows that you copy the formulae down to.

    • sandipgumtya's avatar
      sandipgumtya
      Copper Contributor
      May 28, 2020

      Riny_van_Eekelen 

      Thanks.I think this will work. but i would like to understand why was that happening?

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        May 28, 2020

        sandipgumtya The zero that resulted from your original formulae is due to the inclusion of empty cells in the list you were taking unique cells from. With the dynamic range "MyList", there are no empty cells to consider. Hence, no zero at the end.