Formula for non-duplicate values

A Rose

Hi,

Is there any formula to populate only 1 value of each value listed [Column1]?

I.e., column 1 has something like the below,

45

56

45

55

55

56

46

56

Is there any formula to only show the below?

45

46

55

56

Thank you!

Genevieve P.

Hi @A Rose

Yes! But it will gather together all the separate values into one cell, is that what you were looking for? There's a DISTINCT Function we can use, like so:

=JOIN(DISTINCT([Column1]:[Column1]), ", ")

However keep in mind that all values must be of the same data type in order for the function to calculate (ex. all must be numerical, or all must be text).

Let me know if I've misunderstood what you're looking to do and I'd be happy to help further.

Cheers,

Genevieve

Kelly Moore

Hey @A Rose

Building on Genevieve's answer, to have the answers wrapped try CHAR(10) as the delimiter.

=JOIN(DISTINCT([Column1]:[Column1]), CHAR(10))

The CHAR(10) is ASCII code for a new line.

You must set your column format to wrapped text for the data to appear 'wrapped'.

cheers

Kelly

Genevieve P.

Hi @A Rose

Yes! But it will gather together all the separate values into one cell, is that what you were looking for? There's a DISTINCT Function we can use, like so:

=JOIN(DISTINCT([Column1]:[Column1]), ", ")

However keep in mind that all values must be of the same data type in order for the function to calculate (ex. all must be numerical, or all must be text).

Let me know if I've misunderstood what you're looking to do and I'd be happy to help further.

Cheers,

Genevieve

A Rose

Hi @Genevieve P.

That worked!

would be best though if we can split it in seperate row, either 1 value per row, or when doing "Wrap" it should be on seperate row so we can read it better.

Btw. it worked even though it's all a value, and a number. I.e. "Value 55"

Thank you!

Kelly Moore

Hey @A Rose

Building on Genevieve's answer, to have the answers wrapped try CHAR(10) as the delimiter.

=JOIN(DISTINCT([Column1]:[Column1]), CHAR(10))

The CHAR(10) is ASCII code for a new line.

You must set your column format to wrapped text for the data to appear 'wrapped'.

cheers

Kelly

A Rose

Hi @Kelly Moore ,

Amazing! thanks so much!

Can we go further and have it sorted A to Z in the formula?

Thank you!

L_123

One value / row is easier than sorting it directly. You can do it with 2 formulas.

1st cell top of return column: =index(distinct([Col1]:[Col1]),1

2nd cell and dragged down: =index(distinct([Col1]:[Col1]),1+count(CurrentColumn$1:CurrentColumn1

then use a report to sort the results