Merging A list to show unique value/identifier

Syed MuhafzalSyed Muhafzal ✭✭✭✭
edited 05/29/21 in Formulas and Functions
05/29/21 Edited 05/29/21
Accepted

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

Best Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Syed MuhafzalSyed Muhafzal ✭✭✭✭
    Accepted Answer

    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åAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Syed Muhafzal

    Have you activated the Wrap feature?


    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Syed MuhafzalSyed Muhafzal ✭✭✭✭
    Accepted Answer

    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åAndrée Starå ✭✭✭✭✭
    Accepted Answer

    @Syed Muhafzal

    Have you activated the Wrap feature?


    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Syed MuhafzalSyed 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åAndrée Starå ✭✭✭✭✭

    @Syed Muhafzal

    Excellent!

    You're more than welcome!

    Thanks for the kind words!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.