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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 201 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!