Returning results for identifier as a single identifier or within a join from the same column
Hello! I have a complicated formula that I need help writing. I have one sheet with three columns, Column A is the 'Created' column, Column B is the score given via form, Column C is either a single identifier or joined identifiers based on the form selection. I want to pull the latest score into another sheet which lists each identifier as a single record. So the formula needs to recognize the Max date as well as the identifier that may be contained within a join or a single identifier. Any gurus out there that want to take this one.
Answers
-
I ended up adding a help column of joined identifiers and basically saying IFERROR (NOMATCH) on the single identifier, then use the new joined column, and IFERROR still " ". Here is the formula:
=IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Single Identifier]@row, {Column A}, MAX(COLLECT({Column A}, {Column C}, [Single Identifier]@row))), 1), IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Joined Identifier]@row, {Column A}, MAX(COLLECT({Column A},{Column C}, [Joined Identifier]@row))), 1), ""))
-
Hi @Andrea_F
Thanks for posting your solution! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!