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

[freeb1893]

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:

LABEL

PKEYNUM

SUMMARY

PNAME

PNAME_1

'STORYPOINTS'

NUMBERVALUE

CREATED

UPDATED

RESOLUTIONDATE

REPORTER

ASSIGNEE

CURRENT STATUS

CHANGE AUTHOR

CHANGE DATE

FIELD CHANGED

OLDSTRING

NEWSTRING

EDGE-13901

Story

Trivial

Story Points

1​

7/27/2022 12:51:16​

status

Gathering Requirements

Cancelled

EDGE-13901

Story

Trivial

Story Points

1​

7/27/2022 12:52:30​

status

Cancelled

Cancelled

EDGE-13901

Story

Trivial

Story Points

1​

11/8/2022 07:55:42​

status

Open

Cancelled

EDGE-13904

Story

Minor

Story Points

6/30/2022 07:30:21​

status

Pre-Grooming

Cancelled

EDGE-13905

Story

Major

Story Points

7/6/2022 09:18:07​

priority

Major

External Block

EDGE-13905

Story

Major

Story Points

7/18/2022 08:03:37​

priority

External Block

Major

EDGE-13905

Story

Major

Story Points

7/18/2022 12:55:47​

status

Gathering Requirements

Cancelled

EDGE-13906

Story

Minor

Story Points

11/8/2022 07:56:05​

status

Open

Cancelled

EDGE-13907

Story

Minor

Story Points

7/19/2022 09:16:50​

status

Gathering Requirements

Cancelled

EDGE-13908

Story

Minor

Story Points

7/25/2022 11:00:25​

status

Gathering Requirements

Cancelled

EDGE-13908

Story

Minor

Story Points

7/26/2022 13:33:00​

status

Cancelled

Cancelled

EDGE-13908

Story

Minor

Story Points

7/27/2022 10:41:39​

status

Cancelled

Cancelled

EDGE-13908

Story

Minor

Story Points

7/27/2022 12:57:57​

status

Cancelled

Cancelled

EDGE-13909

Story

Major

Story Points

7/5/2022 13:29:16​

status

Merge Done

Cancelled

EDGE-13910

Story

Minor

Story Points

7/11/2022 09:15:54​

status

Gathering Requirements

Cancelled

EDGE-13911

Story

Minor

Story Points

8/2/2022 12:56:23​

status

Open

Cancelled

EDGE-13952

Story

Critical

Story Points

7/18/2022 09:24:14​

status

Gathering Requirements

Cancelled

EDGE-13987

Story

External Block

Story Points

7/18/2022 09:27:52​

priority

Critical

External Block

EDGE-13987

Story

External Block

Story Points

7/28/2022 11:52:57​

status

Gathering Requirements

Cancelled

EDGE-13998

Story

Critical

Story Points

7/18/2022 09:26:01​

status

Gathering Requirements

Cancelled

EDGE-14003

Story

Critical

Story Points

7/18/2022 09:26:27​

status

Gathering Requirements

Cancelled

EDGE-14008

Story

Major

Story Points

45103.47​

6/26/2023 11:14:24​

status

Ready for Release

Completed

EDGE-14009

Story

Critical

Story Points

44797.54​

8/24/2022 12:53:57​

status

Ready for Release

Completed

EDGE-14026

Story

Major

Story Points

45103.47​

7/19/2022 13:17:09​

status

Ready for Code Review

Cancelled

EDGE-14026

Story

Major

Story Points

45103.47​

6/26/2023 11:11:27​

status

Ready for Release

Completed

EDGE-14027

Story

Major

Story Points

44771.38​

7/29/2022 09:12:41​

status

Ready for Release

Completed

EDGE-14028

Story

Major

Story Points

8/8/2022 09:08:29​

status

Gathering Requirements

Cancelled

EDGE-14035

Story

Major

Story Points

44770.49​

7/28/2022 11:52:22​

status

Ready for Release

Completed

EDGE-14038

Story

Major

Story Points

44768.56​

7/26/2022 13:32:41​

status

Ready for Release

Completed

EDGE-14041

Story

Major

Story Points

45103.46​

6/26/2023 11:03:52​

status

Ready for Release

Completed

EDGE-14042

Story

Major

Story Points

45103.46​

6/26/2023 11:01:15​

status

Ready for Release

Completed

EDGE-14043

Story

Major

Story Points

45103.47​

6/26/2023 11:11:44​

status

Ready for Release

Completed

EDGE-14049

Story

Major

Story Points

8/23/2023 14:14:05​

status

Open

Cancelled

EDGE-14050

Story

Major

Story Points

8/23/2023 14:12:31​

status

Open

Cancelled

EDGE-14052

Story

Major

Story Points

8/23/2023 14:22:06​

status

Open

Cancelled

EDGE-14054

Story

Major

Story Points

44862.51​

10/28/2022 12:16:55​

status

Ready for Release

Completed

EDGE-14055

Story

Major

Story Points

8/24/2023 18:21:42​

status

Open

Cancelled

EDGE-14056

Story

Major

Story Points

8/16/2022 14:19:17​

status

Open

Cancelled

EDGE-14061

Story

Minor

Story Points

2/13/2023 10:48:28​

status

Open

Cancelled

EDGE-14064

Story

Minor

Story Points

44775.42​

8/2/2022 09:58:17​

status

Ready for Release

Completed

EDGE-14065

Story

Critical

Story Points

8​

45103.46​

6/26/2023 11:07:30​

status

Ready for Release

Completed

EDGE-14074

Story

Major

Story Points

3​

9/22/2022 13:58:41​

priority

Major

External Block

EDGE-14074

Story

Major

Story Points

3​

10/19/2022 09:06:51​

priority

External Block

Major

EDGE-14074

Story

Major

Story Points

3​

10/19/2022 10:18:28​

status

Development

Cancelled

EDGE-14102

Story

Major

Story Points

8/23/2023 14:23:42​

status

Open

Cancelled

EDGE-14110

Story

Major

Story Points

5/24/2023 09:01:36​

status

Open

Cancelled

EDGE-14111

Story

Major

Story Points

8/23/2023 14:25:18​

status

Open

Cancelled

EDGE-14125

Story

Critical

Story Points

8/24/2023 18:11:10​

status

Open

Cancelled

EDGE-14139

Story

Critical

Story Points

5​

44845.59​

10/11/2022 14:02:54​

status

Ready for Release

Completed

EDGE-14146

Story

Critical

Story Points

45103.47​

6/26/2023 11:12:17​

status

Ready for Release

Completed

EDGE-14173

Story

Critical

Story Points

44788.6​

8/15/2022 14:21:57​

status

Ready for Release

Completed

EDGE-14180

Story

Critical

Story Points

44790.53​

8/17/2022 12:49:01​

status

Ready for Release

Completed

EDGE-14181

Story

Critical

Story Points

44782.63​

8/9/2022 15:07:30​

status

Ready for Release

Completed

EDGE-14188

Story

Critical

Story Points

44782.68​

8/9/2022 16:16:03​

status

Ready for Release

Completed

EDGE-14199

Story

Major

Story Points

45103.47​

8/23/2022 08:25:52​

priority

Major

External Block

EDGE-14199

Story

Major

Story Points

45103.47​

8/23/2022 08:31:09​

priority

External Block

Blocker

EDGE-14199

Story

Major

Story Points

45103.47​

8/23/2022 08:31:23​

priority

Blocker

External Block

EDGE-14199

Story

Major

Story Points

45103.47​

9/1/2022 16:17:50​

priority

External Block

Major

EDGE-14199

Story

Major

Story Points

45103.47​

6/26/2023 11:13:10​

status

Ready for Release

Completed

EDGE-14215

Story

Minor

Story Points

44816.39​

9/12/2022 09:14:36​

status

Ready for Release

Completed

EDGE-14222

Story

Critical

Story Points

44791.35​

8/18/2022 08:22:08​

status

Ready for Release

Completed

EDGE-14239

Story

Critical

Story Points

44798.37​

8/25/2022 08:48:34​

status

Ready for Release

Completed

EDGE-14240

Story

Critical

Story Points

3​

10/10/2022 17:38:13​

priority

Critical

External Block

EDGE-14240

Story

Critical

Story Points

3​

10/12/2022 11:45:30​

priority

External Block

Critical

EDGE-14241

Story

Critical

Story Points

44798.37​

8/25/2022 08:51:10​

status

Ready for Release

Completed

EDGE-14242

Story

Critical

Story Points

8/26/2022 09:27:04​

priority

Critical

External Block

EDGE-14242

Story

Critical

Story Points

9/19/2022 09:27:24​

priority

External Block

Critical

EDGE-14242

Story

Critical

Story Points

10/3/2022 09:21:10​

status

Development

Cancelled

EDGE-14259

Story

Critical

Story Points

44812.47​

9/8/2022 11:21:32​

status

Ready for Release

Completed

EDGE-14260

Story

Critical

Story Points

44805.43​

9/1/2022 10:19:54​

status

Ready for Release

Completed

EDGE-14271

Story

Major

Story Points

45103.45​

6/26/2023 10:41:43​

status

Ready for Release

Completed

EDGE-14286

Story

Critical

Story Points

5​

44854.92​

10/20/2022 22:03:41​

Fix Version

EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20

EDGE-14286

Story

Critical

Story Points

5​

44854.92​

10/20/2022 22:03:51​

status

Ready for Release

Completed

EDGE-14289

Story

Critical

Story Points

8​

44854.92​

10/20/2022 22:04:43​

status

Ready for Release

Completed

EDGE-14289

Story

Critical

Story Points

8​

44854.92​

10/20/2022 22:04:43​

Fix Version

EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20

EDGE-14292

Story

Critical

Story Points

8/31/2022 13:50:30​

Link

This issue relates to EDGE-14233

EDGE-14292

Story

Critical

Story Points

8/31/2022 13:51:25​

summary

Revert Code Changes made in EDGE-14233 - Group to Group Transfers

EDGE to Cloud - Revert Code Changes made in EDGE-14233 - Group to Group Transfers

EDGE-14292

Story

Critical

Story Points

8/31/2022 15:22:19​

labels

E2C_Development

EDGE-14292

Story

Critical

Story Points

9/2/2022 09:06:04​

Link

This issue relates to EDGE-14301

EDGE-14292

Story

Critical

Story Points

9/14/2022 17:09:45​

Workflow

Jira Status Simplification POC V2

EFM Story Workflow

EDGE-14292

Story

Critical

Story Points

1/16/2023 11:02:32​

Workflow

EFM Story Workflow

EFM Story Workflow Beta

EDGE-14292

Story

Critical

Story Points

3/14/2023 17:55:15​

Epic Link

EDGE-15288

EDGE-14292

Story

Critical

Story Points

4/27/2023 16:07:54​

Workflow

EFM Story Workflow Beta

EFM Story Workflow

EDGE-14292

Story

Critical

Story Points

8/14/2023 10:04:25​

labels

E2C_Development

E2C E2C_Development

EDGE-14292

Story

Critical

Story Points

11/1/2023 14:18:32​

labels

E2C E2C_Development

E2C E2C_Development WS2

EDGE-14305

Story

Critical

Story Points

11/7/2022 16:39:16​

status

Open

Cancelled

EDGE-14311

Story

Critical

Story Points

3​

44854.92​

10/20/2022 22:05:14​

Fix Version

EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20

EDGE-14311

Story

Critical

Story Points

3​

44854.92​

10/20/2022 22:05:14​

status

Ready for Release

Completed

EDGE-14319

Story

Critical

Story Points

45040.69​

4/24/2023 16:35:27​

Fix Version

RLS_04.2023

EDGE-14319

Story

Critical

Story Points

45040.69​

4/24/2023 16:35:33​

status

Ready for Release

Completed

EDGE-14336

Story

Minor

Story Points

10/10/2022 09:09:59​

priority

Minor

External Block

EDGE-14336

Story

Minor

Story Points

10/19/2022 09:27:31​

priority

External Block

Minor

EDGE-14336

Story

Minor

Story Points

10/21/2022 14:06:07​

priority

Minor

External Block

 

[KRice]

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.

=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    )

 

[KRice]

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
	T	U	V
3	Ticket #	Date/Time Moved to External Block	Date/Time Moved from External Block
4	EDGE-13905	7/6/22 09:18:07	7/18/22 08:03:37
5	EDGE-13987	7/18/22 09:27:52	n/a
6	EDGE-14074	9/22/22 13:58:41	10/19/22 09:06:51
7	EDGE-14199	8/23/22 08:25:52	8/23/22 08:31:09
8	EDGE-14199	8/23/22 08:31:23	9/1/22 16:17:50
9	EDGE-14240	10/10/22 17:38:13	10/12/22 11:45:30
10	EDGE-14242	8/26/22 09:27:04	9/19/22 09:27:24
11	EDGE-14336	10/10/22 09:09:59	10/19/22 09:27:31
12	EDGE-14336	10/21/22 14:06:07	n/a
freeb1893

Cell Formulas
Range	Formula
T4:V12	T4	=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.

 

[freeb1893]

=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)

Sorry I forgot to reply to this. Thanks for the the updated version of this formula! This was very helpful!

 

[KRice]

Thanks for the update…happy to help!