Max and index match function
Hi all, i have a sheet where Trailers are logged in multiple time throughout the day and what they had in them such as a PO number or a load number
in a separate sheet i want to type in the Trailer number and i want it to pull the recent entry date of the trailer and the Load number / po that came in that trailer. How can i achive that? if it can pull the time too that will be great.
Answers
-
do you want all the information in one column.If so you need the join function. I can build that formula for you but would take a little bit. If you do separate columns and the sheet your pulling from has a created column. I would do a few helper rows. And run index match in each columns. If you provide this information I can figure the best course for you.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
i want the info in different columns.
also the sheet where we will be pulling the data from it will have a date column and a time column. it does not have a created column, but i can add it.
thankyou for replying
-
@Simran Curry Ok Last question. Are the Tailor numbers ever used again?. If so you would need some sort of Unique identifier. If not a simple Index match for each column to pull the information you need.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
yes Trailer numbers are used multiple times . i need the very recent date and time it was logged.
-
Ok. Try This out and see how it works for you.
=Index(Collect({PO ref},{Trailer Number Ref},[Trailor Number Column]@row,{Date Ref},MAX({Date Ref}),{Time Ref},MAX({Time Ref})))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
i cant seem to get it working.
any way we can connect and you can show me?
-
Absolutely. Do you have teams?
Also I think I see the issue with the formula I created for you. How ever I know a great work around I use all the time. I can implement for you. Adding a helper column in the sheet that you are pulling your information from. It will have an incremental number based on a few criteria.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!