Order duplicates

Options

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 Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Paphyrus
    Options

    Hi:

    this is the origin


    this is the target


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • Paphyrus
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! Happy New Year! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!