Unique items in a List

[sandipgumtya]

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 rid of.

Can you help on this issue? Any other formula or approach is most welcome too.

Excel attached.

[#2]

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

=UNIQUE(D2:D23)

[#3]

@Riny_van_Eekelen 

Hi ,

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

[#4]

@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.

[#5]

@Riny_van_Eekelen 

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

[#6]

@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.