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]

If at all possible, you should provide your table via the XL2BB add-in. After cleaning up a cut-and-paste operation from the html-based table, I managed to get some data to work with. I converted the entire table range into an official Excel table (with headers) and called this source table "Table4". This conversion then allows use of structured references so that table column headings can be used in formulas rather than referencing ranges based on cell addresses. It is not clear what you want to achieve by "indexing". I would interpret that operation to mean you want to create a new field (column), perhaps called "index1", and the field will contain a unique value so that each of those records can be referenced later. But I suspect you simply want to identify the relevant ticket #'s that satisfy certain criteria.

In the example below, I show how to extract a list of ticket #'s (PKEYNUM) satisfying the filter criterion described for the 1st indexing...this creates a list (an array) of ticket #'s where the [NEWSTRING] column contains "External Block". A simple FILTER function can be used to extract these ticket #'s, but because there may be multiple records where the same ticket # has "External Block" in the [NEWSTRING] column, I've wrapped FILTER with a UNIQUE function to trim down the list to only unique ticket #'s of interest. See the results spilled down from X4.

Then confusion sets in: I don't understand what is meant by...

...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

A formula in Z4 spills results of a FILTER/MATCH combination, where all records associated with the ticket #'s previously identified in X4# are shown. Could you refer to this list (Z4#) and explain what should be returned, please? If we take ticket EDGE-14199 as an example, we have 5 records shown, and 2 of them have [NEWSTRING]="External Block", but the timestamps ([CHANGE DATE]) are different. What should happen with this list? Similarly, what do you want to see for the other ticket #'s where multiple records are shown for each?

EDGE-14199	8/23/22 8:25:52	External Block
EDGE-14199	8/23/22 8:31:09	Blocker
EDGE-14199	8/23/22 8:31:23	External Block
EDGE-14199	9/1/22 16:17:50	Major
EDGE-14199	6/26/23 11:13:10	Completed

MrExcel_20231113.xlsx
	X	Y	Z	AA	AB
2	1st indexing		2nd indexing
3	PKEYNUM		PKEYNUM	CHANGE DATE	NEWSTRING
4	EDGE-13905		EDGE-13905	7/6/22 9:18:07	External Block
5	EDGE-13987		EDGE-13905	7/18/22 8:03:37	Major
6	EDGE-14074		EDGE-13905	7/18/22 12:55:47	Cancelled
7	EDGE-14199		EDGE-13987	7/18/22 9:27:52	External Block
8	EDGE-14240		EDGE-13987	7/28/22 11:52:57	Cancelled
9	EDGE-14242		EDGE-14074	9/22/22 13:58:41	External Block
10	EDGE-14336		EDGE-14074	10/19/22 9:06:51	Major
11			EDGE-14074	10/19/22 10:18:28	Cancelled
12			EDGE-14199	8/23/22 8:25:52	External Block
13			EDGE-14199	8/23/22 8:31:09	Blocker
14			EDGE-14199	8/23/22 8:31:23	External Block
15			EDGE-14199	9/1/22 16:17:50	Major
16			EDGE-14199	6/26/23 11:13:10	Completed
17			EDGE-14240	10/10/22 17:38:13	External Block
18			EDGE-14240	10/12/22 11:45:30	Critical
19			EDGE-14242	8/26/22 9:27:04	External Block
20			EDGE-14242	9/19/22 9:27:24	Critical
21			EDGE-14242	10/3/22 9:21:10	Cancelled
22			EDGE-14336	10/10/22 9:09:59	External Block
23			EDGE-14336	10/19/22 9:27:31	Minor
24			EDGE-14336	10/21/22 14:06:07	External Block
freeb1893

Cell Formulas
Range	Formula
X4:X10	X4	=UNIQUE(FILTER(Table4[PKEYNUM],Table4[NEWSTRING]="External Block"))
Z4:AB24	Z4	=CHOOSECOLS(FILTER(Table4,ISNUMBER(MATCH(Table4[PKEYNUM],X4#,0))),2,15,18)
Dynamic array formulas.

 

[freeb1893]

If at all possible, you should provide your table via the XL2BB add-in. After cleaning up a cut-and-paste operation from the html-based table, I managed to get some data to work with. I converted the entire table range into an official Excel table (with headers) and called this source table "Table4". This conversion then allows use of structured references so that table column headings can be used in formulas rather than referencing ranges based on cell addresses. It is not clear what you want to achieve by "indexing". I would interpret that operation to mean you want to create a new field (column), perhaps called "index1", and the field will contain a unique value so that each of those records can be referenced later. But I suspect you simply want to identify the relevant ticket #'s that satisfy certain criteria.

In the example below, I show how to extract a list of ticket #'s (PKEYNUM) satisfying the filter criterion described for the 1st indexing...this creates a list (an array) of ticket #'s where the [NEWSTRING] column contains "External Block". A simple FILTER function can be used to extract these ticket #'s, but because there may be multiple records where the same ticket # has "External Block" in the [NEWSTRING] column, I've wrapped FILTER with a UNIQUE function to trim down the list to only unique ticket #'s of interest. See the results spilled down from X4.

Then confusion sets in: I don't understand what is meant by...

A formula in Z4 spills results of a FILTER/MATCH combination, where all records associated with the ticket #'s previously identified in X4# are shown. Could you refer to this list (Z4#) and explain what should be returned, please? If we take ticket EDGE-14199 as an example, we have 5 records shown, and 2 of them have [NEWSTRING]="External Block", but the timestamps ([CHANGE DATE]) are different. What should happen with this list? Similarly, what do you want to see for the other ticket #'s where multiple records are shown for each?

EDGE-14199	8/23/22 8:25:52	External Block
EDGE-14199	8/23/22 8:31:09	Blocker
EDGE-14199	8/23/22 8:31:23	External Block
EDGE-14199	9/1/22 16:17:50	Major
EDGE-14199	6/26/23 11:13:10	Completed

MrExcel_20231113.xlsx
	X	Y	Z	AA	AB
2	1st indexing		2nd indexing
3	PKEYNUM		PKEYNUM	CHANGE DATE	NEWSTRING
4	EDGE-13905		EDGE-13905	7/6/22 9:18:07	External Block
5	EDGE-13987		EDGE-13905	7/18/22 8:03:37	Major
6	EDGE-14074		EDGE-13905	7/18/22 12:55:47	Cancelled
7	EDGE-14199		EDGE-13987	7/18/22 9:27:52	External Block
8	EDGE-14240		EDGE-13987	7/28/22 11:52:57	Cancelled
9	EDGE-14242		EDGE-14074	9/22/22 13:58:41	External Block
10	EDGE-14336		EDGE-14074	10/19/22 9:06:51	Major
11			EDGE-14074	10/19/22 10:18:28	Cancelled
12			EDGE-14199	8/23/22 8:25:52	External Block
13			EDGE-14199	8/23/22 8:31:09	Blocker
14			EDGE-14199	8/23/22 8:31:23	External Block
15			EDGE-14199	9/1/22 16:17:50	Major
16			EDGE-14199	6/26/23 11:13:10	Completed
17			EDGE-14240	10/10/22 17:38:13	External Block
18			EDGE-14240	10/12/22 11:45:30	Critical
19			EDGE-14242	8/26/22 9:27:04	External Block
20			EDGE-14242	9/19/22 9:27:24	Critical
21			EDGE-14242	10/3/22 9:21:10	Cancelled
22			EDGE-14336	10/10/22 9:09:59	External Block
23			EDGE-14336	10/19/22 9:27:31	Minor
24			EDGE-14336	10/21/22 14:06:07	External Block
freeb1893

Cell Formulas
Range	Formula
X4:X10	X4	=UNIQUE(FILTER(Table4[PKEYNUM],Table4[NEWSTRING]="External Block"))
Z4:AB24	Z4	=CHOOSECOLS(FILTER(Table4,ISNUMBER(MATCH(Table4[PKEYNUM],X4#,0))),2,15,18)
Dynamic array formulas.

Perhaps I should have included an example of my output. Below is an example of the indexing I'm looking to result with. The first indexing are the 1st and 2nd columns, the 3rd column is my second set of indexing that'll be based on what I indexed with my 1st set of indexing in the 1st and second columns below.

Basically I want a list of tickets / records where there's a record for the ticket being moved to "External Block". There will be duplicate tickets / records in my 1st indexing results, because sometimes a ticket moved to External Block multiple times during it's lifecycle.

The 2nd set of indexing I'm trying to do is attempt to reconcile my 1st indexing results and identify when the ticket moved from External Block to another status (whenever that data is available) after it moved to External Block status. Sometimes there may not be a record of the ticket moving from External Block to another status, where I'd want n/a in those cases. I'd also like to ensure there's no erroneous indexing in the 3rd column by ensuring I only index a date/time when the ticket moved from External Block status if the data/time is after the date/time it moved to External Block

Here is an example of the output I'd be expecting with the indexing calculations:

via 1st set of indexing	via 1st set of indexing	via 2nd set of indexing based on the 1st set of indexing results
Ticket #	Date/Time moved to External Block	Date/Time moved from External Block
EDGE-13905	7/6/2022 09:18:07	7/18/2022 08:03:37
EDGE-13987	7/18/2022 09:27:52	n/a
EDGE-14074	9/22/2022 13:58:41	10/19/2022 09:06:51
EDGE-14199	8/23/2022 08:25:52	8/23/2022 08:31:09
EDGE-14199	8/23/2022 08:31:23	9/1/2022 16:17:50
EDGE-14240	10/10/2022 17:38:13	10/12/2022 11:45:30
EDGE-14242	8/26/2022 09:27:04	9/19/2022 09:27:24
EDGE-14336	10/10/2022 09:09:59	10/19/2022 09:27:31
EDGE-14336	10/21/2022 14:06:07	n/a

 

[KRice]

Thank you for the clarification. That second step is messier and I have a version that nearly reproduces your output...except for one result. I suspect that I do not completely understand all of the conditions for the 2nd step. On the following row, could you explain why "n/a" is the answer rather than 7/28/22 11:52:57, which follows the date/time for the "External Block" entry for that same Ticket #. Is it because the [NEWSTRING] is "Cancelled". You must be applying some other rule to exclude this one.

EDGE-13987

7/18/2022 09:27:52

n/a

 

[KRice]

Here is the source table used (it is called "Table4" and has been converted to an official Excel table):

MrExcel_20231113.xlsx
	B													O	P		R
3	PKEYNUM													CHANGE DATE	FIELD CHANGED		NEWSTRING
4	EDGE-13901													7/27/22 12:51:16	status		Cancelled
5	EDGE-13901													7/27/22 12:52:30	status		Cancelled
6	EDGE-13901													11/8/22 7:55:42	status		Cancelled
7	EDGE-13904													6/30/22 7:30:21	status		Cancelled
8	EDGE-13905													7/6/22 9:18:07	priority		External Block
9	EDGE-13905													7/18/22 8:03:37	priority		Major
10	EDGE-13905													7/18/22 12:55:47	status		Cancelled
11	EDGE-13906													11/8/22 7:56:05	status		Cancelled
12	EDGE-13907													7/19/22 9:16:50	status		Cancelled
13	EDGE-13908													7/25/22 11:00:25	status		Cancelled
14	EDGE-13908													7/26/22 13:33:00	status		Cancelled
15	EDGE-13908													7/27/22 10:41:39	status		Cancelled
16	EDGE-13908													7/27/22 12:57:57	status		Cancelled
17	EDGE-13909													7/5/22 13:29:16	status		Cancelled
18	EDGE-13910													7/11/22 9:15:54	status		Cancelled
19	EDGE-13911													8/2/22 12:56:23	status		Cancelled
20	EDGE-13952													7/18/22 9:24:14	status		Cancelled
21	EDGE-13987													7/18/22 9:27:52	priority		External Block
22	EDGE-13987													7/28/22 11:52:57	status		Cancelled
23	EDGE-13998													7/18/22 9:26:01	status		Cancelled
24	EDGE-14003													7/18/22 9:26:27	status		Cancelled
25	EDGE-14008													6/26/23 11:14:24	status		Completed
26	EDGE-14009													8/24/22 12:53:57	status		Completed
27	EDGE-14026													7/19/22 13:17:09	status		Cancelled
28	EDGE-14026													6/26/23 11:11:27	status		Completed
29	EDGE-14027													7/29/22 9:12:41	status		Completed
30	EDGE-14028													8/8/22 9:08:29	status		Cancelled
31	EDGE-14035													7/28/22 11:52:22	status		Completed
32	EDGE-14038													7/26/22 13:32:41	status		Completed
33	EDGE-14041													6/26/23 11:03:52	status		Completed
34	EDGE-14042													6/26/23 11:01:15	status		Completed
35	EDGE-14043													6/26/23 11:11:44	status		Completed
36	EDGE-14049													8/23/23 14:14:05	status		Cancelled
37	EDGE-14050													8/23/23 14:12:31	status		Cancelled
38	EDGE-14052													8/23/23 14:22:06	status		Cancelled
39	EDGE-14054													10/28/22 12:16:55	status		Completed
40	EDGE-14055													8/24/23 18:21:42	status		Cancelled
41	EDGE-14056													8/16/22 14:19:17	status		Cancelled
42	EDGE-14061													2/13/23 10:48:28	status		Cancelled
43	EDGE-14064													8/2/22 9:58:17	status		Completed
44	EDGE-14065													6/26/23 11:07:30	status		Completed
45	EDGE-14074													9/22/22 13:58:41	priority		External Block
46	EDGE-14074													10/19/22 9:06:51	priority		Major
47	EDGE-14074													10/19/22 10:18:28	status		Cancelled
48	EDGE-14102													8/23/23 14:23:42	status		Cancelled
49	EDGE-14110													5/24/23 9:01:36	status		Cancelled
50	EDGE-14111													8/23/23 14:25:18	status		Cancelled
51	EDGE-14125													8/24/23 18:11:10	status		Cancelled
52	EDGE-14139													10/11/22 14:02:54	status		Completed
53	EDGE-14146													6/26/23 11:12:17	status		Completed
54	EDGE-14173													8/15/22 14:21:57	status		Completed
55	EDGE-14180													8/17/22 12:49:01	status		Completed
56	EDGE-14181													8/9/22 15:07:30	status		Completed
57	EDGE-14188													8/9/22 16:16:03	status		Completed
58	EDGE-14199													8/23/22 8:25:52	priority		External Block
59	EDGE-14199													8/23/22 8:31:09	priority		Blocker
60	EDGE-14199													8/23/22 8:31:23	priority		External Block
61	EDGE-14199													9/1/22 16:17:50	priority		Major
62	EDGE-14199													6/26/23 11:13:10	status		Completed
63	EDGE-14215													9/12/22 9:14:36	status		Completed
64	EDGE-14222													8/18/22 8:22:08	status		Completed
65	EDGE-14239													8/25/22 8:48:34	status		Completed
66	EDGE-14240													10/10/22 17:38:13	priority		External Block
67	EDGE-14240													10/12/22 11:45:30	priority		Critical
68	EDGE-14241													8/25/22 8:51:10	status		Completed
69	EDGE-14242													8/26/22 9:27:04	priority		External Block
70	EDGE-14242													9/19/22 9:27:24	priority		Critical
71	EDGE-14242													10/3/22 9:21:10	status		Cancelled
72	EDGE-14259													9/8/22 11:21:32	status		Completed
73	EDGE-14260													9/1/22 10:19:54	status		Completed
74	EDGE-14271													6/26/23 10:41:43	status		Completed
75	EDGE-14286													10/20/22 22:03:41	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
76	EDGE-14286													10/20/22 22:03:51	status		Completed
77	EDGE-14289													10/20/22 22:04:43	status		Completed
78	EDGE-14289													10/20/22 22:04:43	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
79	EDGE-14292													8/31/22 13:50:30	Link		This issue relates to EDGE-14233
80	EDGE-14292													8/31/22 13:51:25	summary		EDGE to Cloud - Revert Code Changes made in EDGE-14233 - Group to Group Transfers
81	EDGE-14292													8/31/22 15:22:19	labels		E2C_Development
82	EDGE-14292													9/2/22 9:06:04	Link		This issue relates to EDGE-14301
83	EDGE-14292													9/14/22 17:09:45	Workflow		EFM Story Workflow
84	EDGE-14292													1/16/23 11:02:32	Workflow		EFM Story Workflow Beta
85	EDGE-14292													3/14/23 17:55:15	Epic Link		EDGE-15288
86	EDGE-14292													4/27/23 16:07:54	Workflow		EFM Story Workflow
87	EDGE-14292													8/14/23 10:04:25	labels		E2C E2C_Development
88	EDGE-14292													11/1/23 14:18:32	labels		E2C E2C_Development WS2
89	EDGE-14305													11/7/22 16:39:16	status		Cancelled
90	EDGE-14311													10/20/22 22:05:14	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
91	EDGE-14311													10/20/22 22:05:14	status		Completed
92	EDGE-14319													4/24/23 16:35:27	Fix Version		RLS_04.2023
93	EDGE-14319													4/24/23 16:35:33	status		Completed
94	EDGE-14336													10/10/22 9:09:59	priority		External Block
95	EDGE-14336													10/19/22 9:27:31	priority		Minor
96	EDGE-14336													10/21/22 14:06:07	priority		External Block
freeb1893

The following single formula performs both major steps you described; however the formula is broken down into several components:

1. First, Table4 is filtered to identify Ticket #'s associated with all [NEWSTRING] entries that are "External Block" (the "feb" formula).
2. Then Table 4 is filtered again to extract all records for the "feb" Ticket #'s...and then the resultant array is trimmed down, keeping only three columns (Ticket #, Change Date, and NewString)...and this array is sorted 1st by Ticket # then by Change Date (the "febtkt" formula).
3. The "febtkt" formula is then broken apart into single column arrays consisting of Ticket # ("tkt"), Change Date ("dt"), and NewString ("str")...to facilitate the next steps.
4. A sequence consisting of the number of rows in "str" is generated ("s"). This is used in the next row-by-row operations.
5. Then the "str" array is taken one row (one cell) at a time to generate an array of integers, beginning with 1 assigned to all cells associated with the 1st instance of "External Block", 2 is assigned to all cells associated with the 2nd instance of "External Block", and so on (the "bin" formula). This binning operation is used to ensure that potential change dates needed in the final step are associated with the correct Move TO External Block.
6. Next, the "str" and "bin" arrays are stepped through one row at a time to determine if: 1) the "str" entry is "External Block", and if so, 2) the next entry has the same bin number (meaning it belongs to the same "External Block" group), and if so, 3) that the next "str" entry is not "Cancelled". If all of these conditions are met, then the Change Date for the next "str" entry is taken as the Date/Time Moved FROM External Block. Otherwise a value of "n/a" is used. In the case where the last array entry is examined, the formula attempts to access the next, non-existent entry, so an error is generated...and the error is trapped with an IFERROR and the value of "n/a" is also used in this case (see the "nxtidx" formula).
7. Finally, the Ticket #, Change Date (TO), and Change Date (FROM) arrays are combined (horizontally stacked) and then filtered to include only those whose "str" [NEWSTRING] value is "External Block".

Please check the conditions I've described in step 6, as the "Cancelled" criteria may not be correct (that is the point of the question I posed in my previous post).

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(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") ) ), FILTER(HSTACK(tkt,dt,nxtidx),str="External Block") )
Dynamic array formulas.

 

[freeb1893]

Thank you for the clarification. That second step is messier and I have a version that nearly reproduces your output...except for one result. I suspect that I do not completely understand all of the conditions for the 2nd step. On the following row, could you explain why "n/a" is the answer rather than 7/28/22 11:52:57, which follows the date/time for the "External Block" entry for that same Ticket #. Is it because the [NEWSTRING] is "Cancelled". You must be applying some other rule to exclude this one.

You’re welcome! And sorry I can’t believe I neglected providing a sample an example output. So that row for EDGE-13987, the 2nd index result is n/a because there is no record that matches that ticket number AND there is record of it going to External Block via Column R of my original data, but there is NOT a record of it going from External Block via Column Q of my original data after the date/time of when it went to External Block via Column R of my original data

Basically I have a record of the ticket moving to External Block, but there is not a record of it moving from External Block to some other status sometime afterwards.

That is a common scenario I could see.

The scenario of the 2nd record of a ticket moving from External Block via Column Q, before the record of it moving to External Block via Column R is much more unlikely, but would like to build that validation of checking the date/time stamp for when it moved to External Block to ensure the record of it moving from External Block occurred after, if possible

 

[freeb1893]

Here is the source table used (it is called "Table4" and has been converted to an official Excel table):

MrExcel_20231113.xlsx
	B													O	P		R
3	PKEYNUM													CHANGE DATE	FIELD CHANGED		NEWSTRING
4	EDGE-13901													7/27/22 12:51:16	status		Cancelled
5	EDGE-13901													7/27/22 12:52:30	status		Cancelled
6	EDGE-13901													11/8/22 7:55:42	status		Cancelled
7	EDGE-13904													6/30/22 7:30:21	status		Cancelled
8	EDGE-13905													7/6/22 9:18:07	priority		External Block
9	EDGE-13905													7/18/22 8:03:37	priority		Major
10	EDGE-13905													7/18/22 12:55:47	status		Cancelled
11	EDGE-13906													11/8/22 7:56:05	status		Cancelled
12	EDGE-13907													7/19/22 9:16:50	status		Cancelled
13	EDGE-13908													7/25/22 11:00:25	status		Cancelled
14	EDGE-13908													7/26/22 13:33:00	status		Cancelled
15	EDGE-13908													7/27/22 10:41:39	status		Cancelled
16	EDGE-13908													7/27/22 12:57:57	status		Cancelled
17	EDGE-13909													7/5/22 13:29:16	status		Cancelled
18	EDGE-13910													7/11/22 9:15:54	status		Cancelled
19	EDGE-13911													8/2/22 12:56:23	status		Cancelled
20	EDGE-13952													7/18/22 9:24:14	status		Cancelled
21	EDGE-13987													7/18/22 9:27:52	priority		External Block
22	EDGE-13987													7/28/22 11:52:57	status		Cancelled
23	EDGE-13998													7/18/22 9:26:01	status		Cancelled
24	EDGE-14003													7/18/22 9:26:27	status		Cancelled
25	EDGE-14008													6/26/23 11:14:24	status		Completed
26	EDGE-14009													8/24/22 12:53:57	status		Completed
27	EDGE-14026													7/19/22 13:17:09	status		Cancelled
28	EDGE-14026													6/26/23 11:11:27	status		Completed
29	EDGE-14027													7/29/22 9:12:41	status		Completed
30	EDGE-14028													8/8/22 9:08:29	status		Cancelled
31	EDGE-14035													7/28/22 11:52:22	status		Completed
32	EDGE-14038													7/26/22 13:32:41	status		Completed
33	EDGE-14041													6/26/23 11:03:52	status		Completed
34	EDGE-14042													6/26/23 11:01:15	status		Completed
35	EDGE-14043													6/26/23 11:11:44	status		Completed
36	EDGE-14049													8/23/23 14:14:05	status		Cancelled
37	EDGE-14050													8/23/23 14:12:31	status		Cancelled
38	EDGE-14052													8/23/23 14:22:06	status		Cancelled
39	EDGE-14054													10/28/22 12:16:55	status		Completed
40	EDGE-14055													8/24/23 18:21:42	status		Cancelled
41	EDGE-14056													8/16/22 14:19:17	status		Cancelled
42	EDGE-14061													2/13/23 10:48:28	status		Cancelled
43	EDGE-14064													8/2/22 9:58:17	status		Completed
44	EDGE-14065													6/26/23 11:07:30	status		Completed
45	EDGE-14074													9/22/22 13:58:41	priority		External Block
46	EDGE-14074													10/19/22 9:06:51	priority		Major
47	EDGE-14074													10/19/22 10:18:28	status		Cancelled
48	EDGE-14102													8/23/23 14:23:42	status		Cancelled
49	EDGE-14110													5/24/23 9:01:36	status		Cancelled
50	EDGE-14111													8/23/23 14:25:18	status		Cancelled
51	EDGE-14125													8/24/23 18:11:10	status		Cancelled
52	EDGE-14139													10/11/22 14:02:54	status		Completed
53	EDGE-14146													6/26/23 11:12:17	status		Completed
54	EDGE-14173													8/15/22 14:21:57	status		Completed
55	EDGE-14180													8/17/22 12:49:01	status		Completed
56	EDGE-14181													8/9/22 15:07:30	status		Completed
57	EDGE-14188													8/9/22 16:16:03	status		Completed
58	EDGE-14199													8/23/22 8:25:52	priority		External Block
59	EDGE-14199													8/23/22 8:31:09	priority		Blocker
60	EDGE-14199													8/23/22 8:31:23	priority		External Block
61	EDGE-14199													9/1/22 16:17:50	priority		Major
62	EDGE-14199													6/26/23 11:13:10	status		Completed
63	EDGE-14215													9/12/22 9:14:36	status		Completed
64	EDGE-14222													8/18/22 8:22:08	status		Completed
65	EDGE-14239													8/25/22 8:48:34	status		Completed
66	EDGE-14240													10/10/22 17:38:13	priority		External Block
67	EDGE-14240													10/12/22 11:45:30	priority		Critical
68	EDGE-14241													8/25/22 8:51:10	status		Completed
69	EDGE-14242													8/26/22 9:27:04	priority		External Block
70	EDGE-14242													9/19/22 9:27:24	priority		Critical
71	EDGE-14242													10/3/22 9:21:10	status		Cancelled
72	EDGE-14259													9/8/22 11:21:32	status		Completed
73	EDGE-14260													9/1/22 10:19:54	status		Completed
74	EDGE-14271													6/26/23 10:41:43	status		Completed
75	EDGE-14286													10/20/22 22:03:41	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
76	EDGE-14286													10/20/22 22:03:51	status		Completed
77	EDGE-14289													10/20/22 22:04:43	status		Completed
78	EDGE-14289													10/20/22 22:04:43	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
79	EDGE-14292													8/31/22 13:50:30	Link		This issue relates to EDGE-14233
80	EDGE-14292													8/31/22 13:51:25	summary		EDGE to Cloud - Revert Code Changes made in EDGE-14233 - Group to Group Transfers
81	EDGE-14292													8/31/22 15:22:19	labels		E2C_Development
82	EDGE-14292													9/2/22 9:06:04	Link		This issue relates to EDGE-14301
83	EDGE-14292													9/14/22 17:09:45	Workflow		EFM Story Workflow
84	EDGE-14292													1/16/23 11:02:32	Workflow		EFM Story Workflow Beta
85	EDGE-14292													3/14/23 17:55:15	Epic Link		EDGE-15288
86	EDGE-14292													4/27/23 16:07:54	Workflow		EFM Story Workflow
87	EDGE-14292													8/14/23 10:04:25	labels		E2C E2C_Development
88	EDGE-14292													11/1/23 14:18:32	labels		E2C E2C_Development WS2
89	EDGE-14305													11/7/22 16:39:16	status		Cancelled
90	EDGE-14311													10/20/22 22:05:14	Fix Version		EDGE Monthly October 2022 / COV Phase 1.16 Release -- 2022-10-20
91	EDGE-14311													10/20/22 22:05:14	status		Completed
92	EDGE-14319													4/24/23 16:35:27	Fix Version		RLS_04.2023
93	EDGE-14319													4/24/23 16:35:33	status		Completed
94	EDGE-14336													10/10/22 9:09:59	priority		External Block
95	EDGE-14336													10/19/22 9:27:31	priority		Minor
96	EDGE-14336													10/21/22 14:06:07	priority		External Block
freeb1893

The following single formula performs both major steps you described; however the formula is broken down into several components:

1. First, Table4 is filtered to identify Ticket #'s associated with all [NEWSTRING] entries that are "External Block" (the "feb" formula).
2. Then Table 4 is filtered again to extract all records for the "feb" Ticket #'s...and then the resultant array is trimmed down, keeping only three columns (Ticket #, Change Date, and NewString)...and this array is sorted 1st by Ticket # then by Change Date (the "febtkt" formula).
3. The "febtkt" formula is then broken apart into single column arrays consisting of Ticket # ("tkt"), Change Date ("dt"), and NewString ("str")...to facilitate the next steps.
4. A sequence consisting of the number of rows in "str" is generated ("s"). This is used in the next row-by-row operations.
5. Then the "str" array is taken one row (one cell) at a time to generate an array of integers, beginning with 1 assigned to all cells associated with the 1st instance of "External Block", 2 is assigned to all cells associated with the 2nd instance of "External Block", and so on (the "bin" formula). This binning operation is used to ensure that potential change dates needed in the final step are associated with the correct Move TO External Block.
6. Next, the "str" and "bin" arrays are stepped through one row at a time to determine if: 1) the "str" entry is "External Block", and if so, 2) the next entry has the same bin number (meaning it belongs to the same "External Block" group), and if so, 3) that the next "str" entry is not "Cancelled". If all of these conditions are met, then the Change Date for the next "str" entry is taken as the Date/Time Moved FROM External Block. Otherwise a value of "n/a" is used. In the case where the last array entry is examined, the formula attempts to access the next, non-existent entry, so an error is generated...and the error is trapped with an IFERROR and the value of "n/a" is also used in this case (see the "nxtidx" formula).
7. Finally, the Ticket #, Change Date (TO), and Change Date (FROM) arrays are combined (horizontally stacked) and then filtered to include only those whose "str" [NEWSTRING] value is "External Block".

Please check the conditions I've described in step 6, as the "Cancelled" criteria may not be correct (that is the point of the question I posed in my previous post).

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(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") ) ), FILTER(HSTACK(tkt,dt,nxtidx),str="External Block") )
Dynamic array formulas.

Sounds good! So I'm not familiar with "I converted the entire table range into an official Excel table (with headers) and called this source table "Table4". How do I do that in Excel? So I can do that on my end and then check your formulas?

 

[KRice]

To convert a range of data to an official table, click anywhere in the range and then Ctrl-t, which will pop up a window where you'll see that Excel has guessed at what the entire table range should be. Usually that is correct...but confirm the range reference is correct, and since your range also has column headings, leave the check box "checked" so that the newly formed table will utilize your headings. You will then probably see some visual indication that the range has been converted into a table. Click anywhere in the table and you should see "Table Design" appear in an upper level menu item. Click on Table Design and look near the upper left corner where where Table Name: appears. That is where you can edit the name of the table.

Going back to the EDGE-13987 discussion, I'm not following your explanation. Here is what the original table contains for that ticket #:

EDGE-13987													7/18/22 9:27:52	priority	Critical	External Block
EDGE-13987													7/28/22 11:52:57	status	Gathering Requirements	Cancelled

So we see EDGE-13987 changing from [OLDSTRING] of "Critical" to [NEWSTRING] of "External Block", and then about 10 days later, there is an entry for [OLDSTRING] of "Gathering Requirements" to [NEWSTRING] of "Cancelled". I may have answered my own question...I believe we need to see some subsequent entry where [OLDSTRING] is "External Block", and since that is not the case for the only EDGE-13987 entry that follows the assignment to "External Block", we should not use this subsequent entry. Is that correct?

 

[freeb1893]

To convert a range of data to an official table, click anywhere in the range and then Ctrl-t, which will pop up a window where you'll see that Excel has guessed at what the entire table range should be. Usually that is correct...but confirm the range reference is correct, and since your range also has column headings, leave the check box "checked" so that the newly formed table will utilize your headings. You will then probably see some visual indication that the range has been converted into a table. Click anywhere in the table and you should see "Table Design" appear in an upper level menu item. Click on Table Design and look near the upper left corner where where Table Name: appears. That is where you can edit the name of the table.

Going back to the EDGE-13987 discussion, I'm not following your explanation. Here is what the original table contains for that ticket #:

EDGE-13987													7/18/22 9:27:52	priority	Critical	External Block
EDGE-13987													7/28/22 11:52:57	status	Gathering Requirements	Cancelled

So we see EDGE-13987 changing from [OLDSTRING] of "Critical" to [NEWSTRING] of "External Block", and then about 10 days later, there is an entry for [OLDSTRING] of "Gathering Requirements" to [NEWSTRING] of "Cancelled". I may have answered my own question...I believe we need to see some subsequent entry where [OLDSTRING] is "External Block", and since that is not the case for the only EDGE-13987 entry that follows the assignment to "External Block", we should not use this subsequent entry. Is that correct?

Got it! Thanks! Yeah you know what, I think you got it! It appears the data is processing as I was hoping it would! Thanks so much for helping me figure this one out!

 

[freeb1893]

To convert a range of data to an official table, click anywhere in the range and then Ctrl-t, which will pop up a window where you'll see that Excel has guessed at what the entire table range should be. Usually that is correct...but confirm the range reference is correct, and since your range also has column headings, leave the check box "checked" so that the newly formed table will utilize your headings. You will then probably see some visual indication that the range has been converted into a table. Click anywhere in the table and you should see "Table Design" appear in an upper level menu item. Click on Table Design and look near the upper left corner where where Table Name: appears. That is where you can edit the name of the table.

Going back to the EDGE-13987 discussion, I'm not following your explanation. Here is what the original table contains for that ticket #:

EDGE-13987													7/18/22 9:27:52	priority	Critical	External Block
EDGE-13987													7/28/22 11:52:57	status	Gathering Requirements	Cancelled

So we see EDGE-13987 changing from [OLDSTRING] of "Critical" to [NEWSTRING] of "External Block", and then about 10 days later, there is an entry for [OLDSTRING] of "Gathering Requirements" to [NEWSTRING] of "Cancelled". I may have answered my own question...I believe we need to see some subsequent entry where [OLDSTRING] is "External Block", and since that is not the case for the only EDGE-13987 entry that follows the assignment to "External Block", we should not use this subsequent entry. Is that correct?

Kind of wanting to better understand what's going on with this formula, what is the "feb" in the formula?