Here is a sample data set:
I want to index the above data based on matching criteria, and then do additional indexing based on matching criteria and identifying the record with the closest Change Date to my prior indexed data.
For example, I want to index the above to find the Change Date for when Field Changed is "status" and the OldString was "Ready to Develop" or all unique IssueNum, and then furthermore in an adjacent cell result cell I want to index the same data to find the Change Date that is closest in time to the previous indexed Change Date for "status" change, but rather where the Field Changed is "assignee", and then in another adjacent cell result I want to index the NewString based on the Change Date that is closest in time to the previous indexed Change Date for "status" change and the Field Changed is "assignee".
Anyone know how to put that into a formula? Thanks in advance
ISSUENUM | CHANGE DATE | FIELD CHANGED | OLDSTRING | NEWSTRING |
13283 | 3/28/2022 1:23 PM | assignee | Doe, John | |
13293 | 3/29/2022 2:07 PM | assignee | Doe, John | Doe, Jane |
13293 | 3/30/2022 8:41 AM | assignee | Doe, Jane | |
13283 | 4/4/2022 8:35 PM | assignee | Doe, John | |
13283 | 5/26/2022 10:16 AM | status | Open | Cancelled |
13901 | 6/27/2022 3:43 PM | priority | Critical | Trivial |
13904 | 6/28/2022 7:49 AM | assignee | Akwaa, Joseph |
I want to index the above data based on matching criteria, and then do additional indexing based on matching criteria and identifying the record with the closest Change Date to my prior indexed data.
For example, I want to index the above to find the Change Date for when Field Changed is "status" and the OldString was "Ready to Develop" or all unique IssueNum, and then furthermore in an adjacent cell result cell I want to index the same data to find the Change Date that is closest in time to the previous indexed Change Date for "status" change, but rather where the Field Changed is "assignee", and then in another adjacent cell result I want to index the NewString based on the Change Date that is closest in time to the previous indexed Change Date for "status" change and the Field Changed is "assignee".
Anyone know how to put that into a formula? Thanks in advance