Index formula and the duplication in the other file
Hi Dear
I have use the index formula the to pull the data from file 2 to file 1 , its work , however in some cases I have under the same employee name in (file 2 ) two different submission date , in my formula I pull only the first submission date from file 2 to file one ,And now I need your help to create other formula to pull the other submission date in another column in file 1 ,
Note: I have only two different submission date in file 2 no more so I think we can first choose the earlier date and then we can choose the latest date.
Answers
-
If you use INDEX(COLLECT()) you can choose which value you'd like to return, as COLLECT will filter a range down for you, then you use the INDEX function to choose which row you'd like to return.
Using your ranges above, change your formula to below, with N being the nth value you'd like to return (so 1 would return 4/4/24, and 2 would return 5/2/24)
=INDEX(COLLECT({img_sub}, {img_name}, Name@row), N)
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Thank you for your reply Jason Tarpinian , But in the photo this is only example as I have many names have two time submission date So in my formula its chose the first raw if duplicated and I want other formula to show the second raw of submission date in another cell in the file one.
-
If I'm understanding correctly, that's what the "N" in the formula is for. In my example below, the first formula pulls in the first match for Employee name (in red), using 1 for the index row. For the 2nd, in green, it pulls the second value. You can continue this for as many as you'd need, or use MAX/MIN if that makes more sense. Or you can use the JOIN function to show all dates within a collected range at once CHAR(10) is a line break to see all dates in a single cell.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Many thanks for you Jason Tarpinian , The issue is solved ,you are the hero
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!