Array formula to create unique list not giving desired result

[jb]

Hello helpers,

I have column A in my worksheet in which I am pasting list of values which contains duplicates. Number of values to be pasted will vary every time.

Now I want to create unique list in column C. I have used one array formula in column C to generate unique list. But my formula is giving an addition 0 at the end of unique list which is not required.

I also want to create one COPY button which will select the unique list for copy so that I can use ctrl+v in another sheet to paste the unique list.

It would be great if unique list will be sorted.

Sample sheet is attached.

 

[vletm]

jb
Could You use Advance Filter instead any formulas?

... after that You could sort manually Your list.

 

[jb]

jb
Could You use Advance Filter instead any formulas?
View attachment 77997
... after that You could sort manually Your list.

Sir, I need to do this unique list generation on and often. So I need formula only. Actually my formula is working. Only thing is last 0 to be removed. And I know the reason why it coming.
I have used a1 to a500 in formula considering maximum 500 entries will come.
If I have 100 entries and if I will use a1 to a100 in formula then last 0 will not appear in unique list.
But as I mentioned that every time number of entries will vary. So I cant use fix range in formula.
Even if someone can help me to write vba code on copy button to create sorted list and copy sorted list excluding last 0, it would be great.

 

[vletm]

jb
You could solve all with VBA,
but as You've opened Your thread in Ask an Excel Question
... I skipped that VBA-part.

 

[Peter Bartholomew]

If you are using 365, a simple formula will generate a sorted list of distinct values.
= SORT(UNIQUE(FILTER(source, source<>"")))
Copy/paste is either manual or requires VBA.

 

[jb]

If you are using 365, a simple formula will generate a sorted list of distinct values.
= SORT(UNIQUE(FILTER(source, source<>"")))
Copy/paste is either manual or requires VBA.

I am not using 365.

 

[jb]

jb
You could solve all with VBA,
but as You've opened Your thread in Ask an Excel Question
... I skipped that VBA-part.

I am creating thread in VBA forum.

 

[vletm]

jb
This thread can move to VBA Macros
... done.

 

[KenU]

I have attached a formula-based solution that should work for versions other than 365. Since I am using 365, I cannot test this fully. You may need to slightly alter the formula in cell J2 by wrapping it with an IFERROR and array entering it over the expected range (select the range and confirm with CSE). It does require defining a named formula in the name manager. The attached is an updated version of your workbook with the solution.

Hope that helps.

Regards,
Ken