Merging A list to show unique value/identifier

Syed Muhafzal

Hi Smarties,

I am wondering if anyone could share some ideas on this.

I have a list of available products. Some of these products are repetitive in the list. For example

I would like to show a list of AVAILABLE products in the dashboard, but without the duplicated products. So ideally, it should be showing in the dashboard as below;

Product A

Product B

Product C

Product D

Since this product list is using an API from another source, it keeps throwing all product names so I will always get a duplicated list whenever it is being updated.

Question - is report the best solution to combine this? Does anyone have any experience with formulas or other features that you could recommend (that can b used frequently as new products will be updated from time to time?)

Thanks!

Syed

Andrée Starå

Hi @Syed Muhafzal

I hope you're well and safe!

Try something like this.

=JOIN(DISTINCT(COLLECT(Primary:Primary; Primary:Primary; <>"")); CHAR(10) + CHAR(10))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Syed Muhafzal

Hi Andree!

Good Day!

Thank you.

The result of the above formula returns in a single cell as Product A Product B Product C Product D.

What I did was, I created a Metric widget in the dashboard with grid data pointing to the cell above, and it shows only the four products. However, I may need to manually refresh the widget data with a new grid when a new distinct product being updated. Might work for now I guess :)

Thanks

Syed

Andrée Starå

@Syed Muhafzal

Have you activated the Wrap feature?

Andrée Starå

Hi @Syed Muhafzal

I hope you're well and safe!

Try something like this.

=JOIN(DISTINCT(COLLECT(Primary:Primary; Primary:Primary; <>"")); CHAR(10) + CHAR(10))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Syed Muhafzal

Hi Andree!

Good Day!

Thank you.

The result of the above formula returns in a single cell as Product A Product B Product C Product D.

What I did was, I created a Metric widget in the dashboard with grid data pointing to the cell above, and it shows only the four products. However, I may need to manually refresh the widget data with a new grid when a new distinct product being updated. Might work for now I guess :)

Thanks

Syed

Andrée Starå

@Syed Muhafzal

Have you activated the Wrap feature?

Syed Muhafzal

Hi Andree!

Perfect! I totally forgot about the Wrap feature LOL! I have also moved this formula to Sheet Summary - and use this as the data for the dashboard. Since the value is on the entire Primary column, any updates to the product list will be updated in the summary and dashboard.

You're a genius. Thanks Andree!

Syed

Andrée Starå

@Syed Muhafzal

Excellent!

You're more than welcome!

Thanks for the kind words!