Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results

F

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
I have a set of data / records regarding changes to a list of tickets

  • My ticket #’s are in Column B
  • My change date/time stamp is in Column O
  • The old value of that data point that changed is in Column Q
  • The new value of that data point that changed is in Column R
  • Some records indicate a particular ticket went to “External Block” status in Column R
  • Other records in this data set indicate a particular ticket went from “External Block” status to some other status in Column Q

I’d like to do some indexing of this data

1st set of indexing:

To start, I’d like to index all the tickets where a change record exists where the new value has changed to “External Block”, identified via that value being found in Column R, in another column I’d like to index the change data/time stamp from Column O that’s associated to the record of when the new value was changed to “External Block”

2nd set of indexing:

Then with that data I just indexed, I’d like to also index IF another record is found that matches the ticket #, and has a record for the new value changed to External Block, and is after the change date/time stamp I just indexed for when a record changed to External Block, but before the next change date/time stamp I just indexed for when a record change to External Block (if applicable – if there is another record of that for the ticket number – won’t always be applicable), where the record changed from External Block (via Column Q) to some other status

Logic considerations with the 2nd set of indexing, sometimes there may be a record for a ticket moving to External Block priority status indexed via my 1st set of indexing, but not a record for it being moved from External Status afterwards at some point to another priority status, where I’d want n/a returned in those cases for the 2nd indexing results

Also logic considerations-wise, I may have multiple cases of External Block being set on a ticket, as well

Anyone able to help me put this into indexing formulas? Thanks in advance!

Here is some sample data:
LABELPKEYNUMSUMMARYPNAMEPNAME_1'STORYPOINTS'NUMBERVALUECREATEDUPDATEDRESOLUTIONDATEREPORTERASSIGNEECURRENT STATUSCHANGE AUTHORCHANGE DATEFIELD CHANGEDOLDSTRINGNEWSTRING
EDGE-13901StoryTrivialStory Points
1​
7/27/2022 12:51:16​
statusGathering RequirementsCancelled
EDGE-13901StoryTrivialStory Points
1​
7/27/2022 12:52:30​
statusCancelledCancelled
EDGE-13901StoryTrivialStory Points
1​
11/8/2022 07:55:42​
statusOpenCancelled
EDGE-13904StoryMinorStory Points
6/30/2022 07:30:21​
statusPre-GroomingCancelled
EDGE-13905StoryMajorStory Points
7/6/2022 09:18:07​
priorityMajorExternal Block
EDGE-13905StoryMajorStory Points
7/18/2022 08:03:37​
priorityExternal BlockMajor
EDGE-13905StoryMajorStory Points
7/18/2022 12:55:47​
statusGathering RequirementsCancelled
EDGE-13906StoryMinorStory Points
11/8/2022 07:56:05​
statusOpenCancelled
EDGE-13907StoryMinorStory Points
7/19/2022 09:16:50​
statusGathering RequirementsCancelled
EDGE-13908StoryMinorStory Points
7/25/2022 11:00:25​
statusGathering RequirementsCancelled
EDGE-13908StoryMinorStory Points
7/26/2022 13:33:00​
statusCancelledCancelled
EDGE-13908StoryMinorStory Points
7/27/2022 10:41:39​
statusCancelledCancelled
EDGE-13908StoryMinorStory Points
7/27/2022 12:57:57​
statusCancelledCancelled
EDGE-13909StoryMajorStory Points
7/5/2022 13:29:16​
statusMerge DoneCancelled
EDGE-13910StoryMinorStory Points
7/11/2022 09:15:54​
statusGathering RequirementsCancelled
EDGE-13911StoryMinorStory Points
8/2/2022 12:56:23​
statusOpenCancelled
EDGE-13952StoryCriticalStory Points
7/18/2022 09:24:14​
statusGathering RequirementsCancelled
EDGE-13987StoryExternal BlockStory Points
7/18/2022 09:27:52​
priorityCriticalExternal Block
EDGE-13987StoryExternal BlockStory Points
7/28/2022 11:52:57​
statusGathering RequirementsCancelled
EDGE-13998StoryCriticalStory Points
7/18/2022 09:26:01​
statusGathering RequirementsCancelled
EDGE-14003StoryCriticalStory Points
7/18/2022 09:26:27​
statusGathering RequirementsCancelled
EDGE-14008StoryMajorStory Points
45103.47​
6/26/2023 11:14:24​
statusReady for ReleaseCompleted
EDGE-14009StoryCriticalStory Points
44797.54​
8/24/2022 12:53:57​
statusReady for ReleaseCompleted
EDGE-14026StoryMajorStory Points
45103.47​
7/19/2022 13:17:09​
statusReady for Code ReviewCancelled
EDGE-14026StoryMajorStory Points
45103.47​
6/26/2023 11:11:27​
statusReady for ReleaseCompleted
EDGE-14027StoryMajorStory Points
44771.38​
7/29/2022 09:12:41​
statusReady for ReleaseCompleted
EDGE-14028StoryMajorStory Points
8/8/2022 09:08:29​
statusGathering RequirementsCancelled
EDGE-14035StoryMajorStory Points
44770.49​
7/28/2022 11:52:22​
statusReady for ReleaseCompleted
EDGE-14038StoryMajorStory Points
44768.56​
7/26/2022 13:32:41​
statusReady for ReleaseCompleted
EDGE-14041StoryMajorStory Points
45103.46​
6/26/2023 11:03:52​
statusReady for ReleaseCompleted
EDGE-14042StoryMajorStory Points
45103.46​
6/26/2023 11:01:15​
statusReady for ReleaseCompleted
EDGE-14043StoryMajorStory Points
45103.47​
6/26/2023 11:11:44​
statusReady for ReleaseCompleted
EDGE-14049StoryMajorStory Points
8/23/2023 14:14:05​
statusOpenCancelled
EDGE-14050StoryMajorStory Points
8/23/2023 14:12:31​
statusOpenCancelled
EDGE-14052StoryMajorStory Points
8/23/2023 14:22:06​
statusOpenCancelled
EDGE-14054StoryMajorStory Points
44862.51​
10/28/2022 12:16:55​
statusReady for ReleaseCompleted
EDGE-14055StoryMajorStory Points
8/24/2023 18:21:42​
statusOpenCancelled
EDGE-14056StoryMajorStory Points
8/16/2022 14:19:17​
statusOpenCancelled
EDGE-14061StoryMinorStory Points
2/13/2023 10:48:28​
statusOpenCancelled
EDGE-14064StoryMinorStory Points
44775.42​
8/2/2022 09:58:17​
statusReady for ReleaseCompleted
EDGE-14065StoryCriticalStory Points
8​
45103.46​
6/26/2023 11:07:30​
statusReady for ReleaseCompleted
EDGE-14074StoryMajorStory Points
3​
9/22/2022 13:58:41​
priorityMajorExternal Block
EDGE-14074StoryMajorStory Points
3​
10/19/2022 09:06:51​
priorityExternal BlockMajor
EDGE-14074StoryMajorStory Points
3​
10/19/2022 10:18:28​
statusDevelopmentCancelled
EDGE-14102StoryMajorStory Points
8/23/2023 14:23:42​
statusOpenCancelled
EDGE-14110StoryMajorStory Points
5/24/2023 09:01:36​
statusOpenCancelled
EDGE-14111StoryMajorStory Points
8/23/2023 14:25:18​
statusOpenCancelled
EDGE-14125StoryCriticalStory Points
8/24/2023 18:11:10​
statusOpenCancelled
EDGE-14139StoryCriticalStory Points
5​
44845.59​
10/11/2022 14:02:54​
statusReady for ReleaseCompleted
EDGE-14146StoryCriticalStory Points
45103.47​
6/26/2023 11:12:17​
statusReady for ReleaseCompleted
EDGE-14173StoryCriticalStory Points
44788.6​
8/15/2022 14:21:57​
statusReady for ReleaseCompleted
EDGE-14180StoryCriticalStory Points
44790.53​
8/17/2022 12:49:01​
statusReady for ReleaseCompleted
EDGE-14181StoryCriticalStory Points
44782.63​
8/9/2022 15:07:30​
statusReady for ReleaseCompleted
EDGE-14188StoryCriticalStory Points
44782.68​
8/9/2022 16:16:03​
statusReady for ReleaseCompleted
EDGE-14199StoryMajorStory Points
45103.47​
8/23/2022 08:25:52​
priorityMajorExternal Block
EDGE-14199StoryMajorStory Points
45103.47​
8/23/2022 08:31:09​
priorityExternal BlockBlocker
EDGE-14199StoryMajorStory Points
45103.47​
8/23/2022 08:31:23​
priorityBlockerExternal Block
EDGE-14199StoryMajorStory Points
45103.47​
9/1/2022 16:17:50​
priorityExternal BlockMajor
EDGE-14199StoryMajorStory Points
45103.47​
6/26/2023 11:13:10​
statusReady for ReleaseCompleted
EDGE-14215StoryMinorStory Points
44816.39​
9/12/2022 09:14:36​
statusReady for ReleaseCompleted
EDGE-14222StoryCriticalStory Points
44791.35​
8/18/2022 08:22:08​
statusReady for ReleaseCompleted
EDGE-14239StoryCriticalStory Points
44798.37​
8/25/2022 08:48:34​
statusReady for ReleaseCompleted
EDGE-14240StoryCriticalStory Points
3​
10/10/2022 17:38:13​
priorityCriticalExternal Block
EDGE-14240StoryCriticalStory Points
3​
10/12/2022 11:45:30​
priorityExternal BlockCritical
EDGE-14241StoryCriticalStory Points
44798.37​
8/25/2022 08:51:10​
statusReady for ReleaseCompleted
EDGE-14242StoryCriticalStory Points
8/26/2022 09:27:04​
priorityCriticalExternal Block
EDGE-14242StoryCriticalStory Points
9/19/2022 09:27:24​
priorityExternal BlockCritical
EDGE-14242StoryCriticalStory Points
10/3/2022 09:21:10​
statusDevelopmentCancelled
EDGE-14259StoryCriticalStory Points
44812.47​
9/8/2022 11:21:32​
statusReady for ReleaseCompleted
EDGE-14260StoryCriticalStory Points
44805.43​
9/1/2022 10:19:54​
statusReady for ReleaseCompleted
EDGE-14271StoryMajorStory Points
45103.45​
6/26/2023 10:41:43​
statusReady for ReleaseCompleted
EDGE-14286StoryCriticalStory Points
5​
44854.92​
10/20/2022 22:03:41​
Fix VersionEDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
EDGE-14286StoryCriticalStory Points
5​
44854.92​
10/20/2022 22:03:51​
statusReady for ReleaseCompleted
EDGE-14289StoryCriticalStory Points
8​
44854.92​
10/20/2022 22:04:43​
statusReady for ReleaseCompleted
EDGE-14289StoryCriticalStory Points
8​
44854.92​
10/20/2022 22:04:43​
Fix VersionEDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
EDGE-14292StoryCriticalStory Points
8/31/2022 13:50:30​
LinkThis issue relates to EDGE-14233
EDGE-14292StoryCriticalStory Points
8/31/2022 13:51:25​
summaryRevert Code Changes made in EDGE-14233 - Group to Group TransfersEDGE to Cloud - Revert Code Changes made in EDGE-14233 - Group to Group Transfers
EDGE-14292StoryCriticalStory Points
8/31/2022 15:22:19​
labelsE2C_Development
EDGE-14292StoryCriticalStory Points
9/2/2022 09:06:04​
LinkThis issue relates to EDGE-14301
EDGE-14292StoryCriticalStory Points
9/14/2022 17:09:45​
WorkflowJira Status Simplification POC V2EFM Story Workflow
EDGE-14292StoryCriticalStory Points
1/16/2023 11:02:32​
WorkflowEFM Story WorkflowEFM Story Workflow Beta
EDGE-14292StoryCriticalStory Points
3/14/2023 17:55:15​
Epic LinkEDGE-15288
EDGE-14292StoryCriticalStory Points
4/27/2023 16:07:54​
WorkflowEFM Story Workflow BetaEFM Story Workflow
EDGE-14292StoryCriticalStory Points
8/14/2023 10:04:25​
labelsE2C_DevelopmentE2C E2C_Development
EDGE-14292StoryCriticalStory Points
11/1/2023 14:18:32​
labelsE2C E2C_DevelopmentE2C E2C_Development WS2
EDGE-14305StoryCriticalStory Points
11/7/2022 16:39:16​
statusOpenCancelled
EDGE-14311StoryCriticalStory Points
3​
44854.92​
10/20/2022 22:05:14​
Fix VersionEDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
EDGE-14311StoryCriticalStory Points
3​
44854.92​
10/20/2022 22:05:14​
statusReady for ReleaseCompleted
EDGE-14319StoryCriticalStory Points
45040.69​
4/24/2023 16:35:27​
Fix VersionRLS_04.2023
EDGE-14319StoryCriticalStory Points
45040.69​
4/24/2023 16:35:33​
statusReady for ReleaseCompleted
EDGE-14336StoryMinorStory Points
10/10/2022 09:09:59​
priorityMinorExternal Block
EDGE-14336StoryMinorStory Points
10/19/2022 09:27:31​
priorityExternal BlockMinor
EDGE-14336StoryMinorStory Points
10/21/2022 14:06:07​
priorityMinorExternal Block
 
Sort by date Sort by votes
See my explanation in post #5. The LET function allows one to define variables: In this case "feb" is the resultant array obtained by applying FILTER to Table4 to obtain only those rows where [NEWSTRING]="External Block"...that's what tells you which Ticket #'s need to be examined (this is equivalent to what you described as the 1st indexing). If you want to get a better idea about what each step does (and what the results of each step look like), try the formula below instead. In this formula, I've named the original last step "origfinal" and then have instructed the LET function to return the result of the "feb" formula. If you want to see what the "febtkt" result looks like, simply replace the final "feb" with febtkt...and so on with other variables.

This formula is not quite correct based on my observation/realization/question above. Right now the formula is taking the Change Date that follows [NEWSTRING]="External Block", but it does not check whether [OLDSTRING]="External Block"...that's the new twist to make it work that way I think you've described. I'll post a correction later this evening. In the mean time, you might want to get comfortable seeing how the different components in the formula work by using this version and editing the argument...so if you want to see what "origfinal" returns, change the last "feb" to origfinal.

Excel Formula: 
=LET(feb,CHOOSECOLS(FILTER(Table4,Table4[NEWSTRING]="External Block"),2),febtkt,SORT(CHOOSECOLS(FILTER(Table4,ISNUMBER(MATCH(Table4[PKEYNUM],feb,0))),2,15,18),{1,2}),tkt,CHOOSECOLS(febtkt,1),  dt,CHOOSECOLS(febtkt,2),  str,CHOOSECOLS(febtkt,3), s,SEQUENCE(ROWS(str)),bin,SCAN(0,str,LAMBDA(a,v,IF(v<>"External Block",a,a+1))),nxtidx,SCAN(0,s,LAMBDA(a,s,IFERROR(IF(AND(INDEX(str,s)="External Block",INDEX(bin,s)=INDEX(bin,s+1),INDEX(str,s+1)<>"Cancelled"),INDEX(dt,s+1),"n/a"),"n/a")  )  ), origfinal,FILTER(HSTACK(tkt,dt,nxtidx),str="External Block"),    feb    )
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Here is a corrected version of the formula. This version eliminates the check for "Cancelled" and instead replaces it with a confirmation that the row below a [NEWSTRING]="External Block" contains {OLDSTRING]="External Block" as a condition for using that 2nd Change Date, otherwise "n/a" is returned. This simplifies the first couple of steps, and I was able to eliminate the febtkt part of the formula and incorporate that idea into the initial feb part.

MrExcel_20231113.xlsx
TUV
3Ticket #Date/Time Moved to External BlockDate/Time Moved from External Block
4EDGE-139057/6/22 09:18:077/18/22 08:03:37
5EDGE-139877/18/22 09:27:52n/a
6EDGE-140749/22/22 13:58:4110/19/22 09:06:51
7EDGE-141998/23/22 08:25:528/23/22 08:31:09
8EDGE-141998/23/22 08:31:239/1/22 16:17:50
9EDGE-1424010/10/22 17:38:1310/12/22 11:45:30
10EDGE-142428/26/22 09:27:049/19/22 09:27:24
11EDGE-1433610/10/22 09:09:5910/19/22 09:27:31
12EDGE-1433610/21/22 14:06:07n/a
freeb1893
Cell Formulas
RangeFormula
T4:V12T4=LET(eba,"External Block", feb,SORT(CHOOSECOLS(FILTER(Table4,(Table4[NEWSTRING]=eba)+(Table4[OLDSTRING]=eba)),2,15,17,18),{1,2}), tkt,CHOOSECOLS(feb,1), dt,CHOOSECOLS(feb,2), stro,CHOOSECOLS(feb,3), strn,CHOOSECOLS(feb,4), s,SEQUENCE(ROWS(feb)), bin,SCAN(0,strn,LAMBDA(a,v,IF(v<>eba,a,a+1))), nxtidx,SCAN(0,s,LAMBDA(a,s,IFERROR(IF(AND(INDEX(strn,s)=eba,INDEX(bin,s)=INDEX(bin,s+1),INDEX(stro,s+1)=eba),INDEX(dt,s+1),"n/a"),"n/a"))), fin,FILTER(HSTACK(tkt,dt,nxtidx),strn=eba), fin)
Dynamic array formulas.
 
Upvote 0
KRice said:
=LET(eba,"External Block", feb,SORT(CHOOSECOLS(FILTER(Table4,(Table4[NEWSTRING]=eba)+(Table4[OLDSTRING]=eba)),2,15,17,18),{1,2}), tkt,CHOOSECOLS(feb,1), dt,CHOOSECOLS(feb,2), stro,CHOOSECOLS(feb,3), strn,CHOOSECOLS(feb,4), s,SEQUENCE(ROWS(feb)), bin,SCAN(0,strn,LAMBDA(a,v,IF(v<>eba,a,a+1))), nxtidx,SCAN(0,s,LAMBDA(a,s,IFERROR(IF(AND(INDEX(strn,s)=eba,INDEX(bin,s)=INDEX(bin,s+1),INDEX(stro,s+1)=eba),INDEX(dt,s+1),"n/a"),"n/a"))), fin,FILTER(HSTACK(tkt,dt,nxtidx),strn=eba), fin)
Click to expand...
Sorry I forgot to reply to this. Thanks for the the updated version of this formula! This was very helpful!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
535
awoohaw
awoohaw
F
  • Question Question
Count # of values that fall within a certain date range
Replies
1
Views
567
RobP
R
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
393
freeb1893
F
F
  • Question Question
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
Replies
4
Views
538
freeb1893
F
M
  • Question Question
Index Match function List based on date
Replies
7
Views
271
Fluff
Fluff

Forum statistics

Threads
1,223,935
Messages
6,175,495
Members
452,649
Latest member
mr_bhavesh

Share this page

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