How do I return DISTINCT values from another sheet based on specific dates within that sheet?

Matt Foss

Hello,

I have a need to return a Distinct Item ID from another sheet based on a date range from another date column within that same external sheet. The formula below works; however, once I run out of Distinct item IDs that meet my date criteria it continues to return results that fall outside that date range.

Current formula:IFERROR(INDEX(DISCTINCT(COLLECT({Item ID},{Create Date},>=$[Start Date]$1,<=$[End Date]$1),{Item ID}), [Row ID]@row,1)),"")

What can I do to ensure the formulaonlyreturns Item IDs between the two dates I specify? I have made an attempt to illustrate the problem below.

In the screenshot above, you can see that my formula is pulling in the correct information initially, but once it runs out of Item-IDs that meet the "date range" criteria in the external sheet, it begins to return Item ID's that are outside the date range (Item-ID in the red box). I would like my formula toonlyreturn Item-IDs between my specified dates.

Any thoughts?

Carson Penticuff

I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post?

=IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), "")

Carson Penticuff

I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post?

=IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), "")

Matt Foss

I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post? <\/p>

=IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), "")<\/p>","bodyRaw":"[{\"insert\":\"I think this is what you are trying to accomplish. I assume you are dragging the formula down, as this will not work as a column formula due to the absolute references. There were a few issues with the formula you posted, maybe there were some typos when transferring it to your post? \\n=IFERROR(INDEX(DISTINCT(COLLECT({Item ID}, {Create Date}, >= $[Start Date]$1, {Create Date}, <= $[End Date]$1)), [Row ID]@row), \\\"\\\")\\n\"}]","format":"rich","dateInserted":"2023-07-22T02:23:22+00:00","insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-07-29T02:47:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386531#Comment_386531","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/386531#Comment_386531

Thank you, Carson! That solved for it.

A lot of the other community answers I found had to be repurposed and honed in on some complex solutions to handling the row ID (to find 1st match, 2nd match, etc.). I believe that's what caused the issue with my original formula.

In any case, this will save a lot of time and add a lot of automation to what was originally a very complex process from an excel extract. Thanks again!

Carson Penticuff

I'm glad I could help!️