Creating a comma separated list from a column of numbers, omitting duplicates, spaces

[Higoten]

Hello Excel forum community! I seek your expert advice again.

What I'm trying to do is take information that is located in a column of numbers, and be automatically forwarded to a single cell with commas between each number, and omitting any duplicates and spaces (empty cells), with them also being in numerical order.

For example:
8765
8765
9803
2011
8765
0008

in six columns would be taken to a single cell as:
0008, 2011, 8765, 9803


In the sample workbook attached, I have information in sheet two, column A and needs to be sent automatically as a comma'ed list to sheet one, B1

On sheet two, the space for potential data input is between A2 and A20

I can include my actual project (with dummy information) if the sample project doesn't explain clearly enough what I need to accomplish.

I only have limited knowledge of programing with VBA,so a code snippet would be preferred.

Thank you in advanced!

[JieJenn]

I created an User Defined Function let me know if that's what you're looking for

Please Login or Register  to view this content.

[Higoten]

Hi jiejenn, thanks for the quick response,

The function you made puts the column into a list with commas, but its not omitting the blank (empty) cells, duplicates, or putting them into numerical order,

Like in the sample project, it is outputting:

,,,,,,,,,,7783222,783222,783222,9,983921,909231,983921,762831,

More of what i was hoping for would be something along the lines of an output of:
9, 762831, 783222, 909231, 983921

Thanks!

[martindwilson]

with a few functions! v1 all same length v 2 as entered

[Higoten]

Thank you Martin!
does just what i was looking for!
+rep

[shaukat]

Hello Higoten,
another option

Please Login or Register  to view this content.

[mikerickson]

The attached has the UDF ConcatIf and the formula
=ConcatIf(Sheet2!A2:A20,"<>",Sheet2!A2:A20,",",TRUE)

Please Login or Register  to view this content.