Order duplicates

Helloo!

How to get order in the list of duplicates(1,2,3...) to after export each value to different colums

For example: spain 90, spain 30, france 20¡, france 22 italy 20


1 2 3

Spain 90 30 -

France 20 22 -

Italy 20 - -


Anybody that can help me?

Thank you in advance

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Paphyrus

    Thank you for clarifying! You can use an INDEX(COLLECT function to bring back a value based on a matching unique identifier across sheets.

    At the end of the INDEX function you can put 1 for the first row that matches in your first column, then swap it for 2 for the second row that matches in your second column, etc.

    For example, in Column 1:

    =INDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 1)

    Column 2:

    =INDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 2)


    However if there isn't a second or third row you'll get an error. In this instance, wrap the whole formula in an IFERROR, like so:

    =IFERROR(NDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 1), "")


    Let me know if that works/makes sense.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Hi @Paphyrus

    Would you be able to post a screen capture of how your source sheet/data is formatted? (But please block out sensitive data).

    You could use a JOIN(COLLECT formula to Join together all the values that have "Spain" or "Italy" in them, however this would bring your data into one cell (versus parsing them out into different columns).

    Another option would be to create a Report and then Group it by the country. This would still show the data in multiple rows, but it would group it together in a clear format. (See: Redesigned Reports with Grouping and Summary Functions)

    Let me know if this makes sense and if either option would work for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Hi:

    this is the origin


    this is the target


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Paphyrus

    Thank you for clarifying! You can use an INDEX(COLLECT function to bring back a value based on a matching unique identifier across sheets.

    At the end of the INDEX function you can put 1 for the first row that matches in your first column, then swap it for 2 for the second row that matches in your second column, etc.

    For example, in Column 1:

    =INDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 1)

    Column 2:

    =INDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 2)


    However if there isn't a second or third row you'll get an error. In this instance, wrap the whole formula in an IFERROR, like so:

    =IFERROR(NDEX(COLLECT({Total Score Column}, {Dept Column}, PROJECT@row), 1), "")


    Let me know if that works/makes sense.

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Thank you ! It works. Happy New YEAR!!!!

  • Wonderful! Happy New Year! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!