Would like to index different data points based on multiple criteria, and then index additional data in the same columns based on date/time proximity

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Here is a sample data set:

ISSUENUMCHANGE DATEFIELD CHANGEDOLDSTRINGNEWSTRING
13283
3/28/2022 1:23 PM​
assigneeDoe, John
13293
3/29/2022 2:07 PM​
assigneeDoe, JohnDoe, Jane
13293
3/30/2022 8:41 AM​
assigneeDoe, Jane
13283
4/4/2022 8:35 PM​
assigneeDoe, John
13283
5/26/2022 10:16 AM​
statusOpenCancelled
13901
6/27/2022 3:43 PM​
priorityCriticalTrivial
13904
6/28/2022 7:49 AM​
assigneeAkwaa, 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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Version 2208 (Build 15601.20680_) Micosoft 365 Apps

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Sounds good. Just added that to my Account Details. Beyond that, any thoughts on how to put my original question / logic into an Excel formula?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top