Order duplicates

Paphyrus

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

Genevieve P.

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

Genevieve P.

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

Hi:

this is the origin

this is the target

Genevieve P.

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

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

Genevieve P.

Wonderful! Happy New Year! 🙂