Excel formula to identify data points that are recurring in sequential months

[rhonllanders]

Hi! I'm having trouble try to figuring out a way to report on data that has a specific error and error owner combo that has not been fixed month over month. The data set contains the full historical data of all months, all errors and all error owners and can only be reported if that exact combo of error/error owner is occurring in sequential months.

The problem comes with the sequential piece. I need to report only if it occurs in back to back months from the current month, so, if the error appeared in July-2023, Sep-2023, Oct-2023 and Nov -2023, I can only report it as aged by 3 months (Sep going forward) because it was resolved during the month of Aug-2023.

The formula I have now is only counting the number of times that same error and error owner combo (4 months) exists within the full historical data set, but I need a formula that will recognize when an error/error owner combo skipped a month in the sequence and then restart the count from the point.

If anyone has an idea on how to make this work, I would be so grateful.

TIA

 

[Peter_SSs]

If anyone has an idea on how to make this work, I would be so grateful.

I think forum helpers would be more likely to be able to suggest something if they could see what the data is like and how it is laid out. Could you construct a smallish set of dummy representative data and post that with XL2BB (so that helpers can easily copy for testing) and explain again referring to the sample data provided?

The formula I have now ..

You could also tell us what that formula is so we might have a starting point.

 

[JamesCanale]

This got very ugly. I hope it is useful. I'm showing all the current errors. you can filter out the ones that you don't want if you can unpack it.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G
1	Error	Owner	Month		Data Date:
2	1	Fred	Jan 2023		Nov 2023
3	6	Mary	Nov 2023
4	1	Fred	Feb 2023		Current Errors
5	2	Harry	Jun 2023		Error No.	Name	Months
6	4	Jamie	Jun 2023		4	Jamie	3
7	4	Jamie	Jul 2023		5	John	2
8	4	Jamie	Sep 2023		6	Mary	1
9	4	Jamie	Oct 2023
10	5	John	Oct 2023
11	4	Jamie	Nov 2023
12	5	John	Nov 2023
Sheet16

Cell Formulas
Range	Formula
E6:G8	E6	=LET(v,A2:B12,w,C2:C12,x,B2:B12,y,A2:A12,z,A2:C12,xx,E2, a,FILTER(v,w=xx), b,FILTER(z,(NOT(ISERROR(MATCH(y,DROP(a,,-1)))))*(NOT(ISERROR(MATCH(x,DROP(a,,1)))))), c,SORT(SORT(b,3,-1),1), cc,TAKE(c,,2), d,TAKE(c,,-1), e,12*YEAR(d)+MONTH(d), ee,DROP(e,1), eee,VSTACK(ee,TAKE(e,1)), g,e-eee, h,SIGN((g<1)+(g>1))*SEQUENCE(ROWS(g)), i,FILTER(cc,h>0), ii,FILTER(h,h>0), j,ii-DROP(VSTACK(0,ii),-1), k,UNIQUE(i), m,DROP(i,,-1)&"-"&DROP(i,,1), mm,DROP(k,,-1)&"-"&DROP(k,,1), n,XMATCH(mm,m,0), o,INDEX(j,n), p,HSTACK(k,o), p)
Dynamic array formulas.

 

[rhonllanders]

I think forum helpers would be more likely to be able to suggest something if they could see what the data is like and how it is laid out. Could you construct a smallish set of dummy representative data and post that with XL2BB (so that helpers can easily copy for testing) and explain again referring to the sample data provided?


You could also tell us what that formula is so we might have a starting point.

Hi! Thanks for the suggestion. I will try to pull something together so that you can better see the scenario that I need to work through.

 

[rhonllanders]

This got very ugly. I hope it is useful. I'm showing all the current errors. you can filter out the ones that you don't want if you can unpack it.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G
1	Error	Owner	Month		Data Date:
2	1	Fred	Jan 2023		Nov 2023
3	6	Mary	Nov 2023
4	1	Fred	Feb 2023		Current Errors
5	2	Harry	Jun 2023		Error No.	Name	Months
6	4	Jamie	Jun 2023		4	Jamie	3
7	4	Jamie	Jul 2023		5	John	2
8	4	Jamie	Sep 2023		6	Mary	1
9	4	Jamie	Oct 2023
10	5	John	Oct 2023
11	4	Jamie	Nov 2023
12	5	John	Nov 2023
Sheet16

Cell Formulas
Range	Formula
E6:G8	E6	=LET(v,A2:B12,w,C2:C12,x,B2:B12,y,A2:A12,z,A2:C12,xx,E2, a,FILTER(v,w=xx), b,FILTER(z,(NOT(ISERROR(MATCH(y,DROP(a,,-1)))))*(NOT(ISERROR(MATCH(x,DROP(a,,1)))))), c,SORT(SORT(b,3,-1),1), cc,TAKE(c,,2), d,TAKE(c,,-1), e,12*YEAR(d)+MONTH(d), ee,DROP(e,1), eee,VSTACK(ee,TAKE(e,1)), g,e-eee, h,SIGN((g<1)+(g>1))*SEQUENCE(ROWS(g)), i,FILTER(cc,h>0), ii,FILTER(h,h>0), j,ii-DROP(VSTACK(0,ii),-1), k,UNIQUE(i), m,DROP(i,,-1)&"-"&DROP(i,,1), mm,DROP(k,,-1)&"-"&DROP(k,,1), n,XMATCH(mm,m,0), o,INDEX(j,n), p,HSTACK(k,o), p)
Dynamic array formulas.

Hi! Thanks so much! I'm excited to check out this formula! Appreciate your help!

 

[Peter_SSs]

I'm excited to check out this formula!

If you still need help after that, could you please ..

1. Give some sample data as requested in post 2
2. If it is not obvious from the sample data, tell if the data is sorted/grouped in any way
3. Give us your current formula as requested in post 2
4. Tell us about how many rows of data there is, or is likely to be, in your overall data.
5. Advise if a vba (macro) solution would be acceptable if one could be found.

 

[rhonllanders]

Hi! My in-office Excel does not provide the Add-in XL2BB so I've created an example within the comments section.

Here is a sample of what the data looks like and the last column needs a formula that will look for the same Task Key, Exception and Issue Key counted only by consecutive months. Example: If exception was resolved in Aug-2023 but reappeared in Sep-2023, it would not include Jun-2023 and Jul-2023 in the # Months Unresolved. The count starts over beginning with Sep-2023 forward.

Please let me know if more detail is needed.

Month	Task Key	Exception	Issue Key	helper	# Months Unresolved
6/1/2023	ABC-1234567890	Overdue Invoice	I543210	ABC-1234567890Overdue InvoiceI543210	1
7/1/2023	ABC-1234567890	Overdue Invoice	I543210	ABC-1234567890Overdue InvoiceI543210	2
9/1/2023	ABC-1234567890	Overdue Invoice	I543210	ABC-1234567890Overdue InvoiceI543210	1
10/1/2023	ABC-1234567890	Overdue Invoice	I543210	ABC-1234567890Overdue InvoiceI543210	2
11/1/2023	ABC-1234567890	Overdue Invoice	I543210	ABC-1234567890Overdue InvoiceI543210	3

 

[rhonllanders]

If you still need help after that, could you please ..

1. Give some sample data as requested in post 2
2. If it is not obvious from the sample data, tell if the data is sorted/grouped in any way
3. Give us your current formula as requested in post 2
4. Tell us about how many rows of data there is, or is likely to be, in your overall data.
5. Advise if a vba (macro) solution would be acceptable if one could be found.

Hi! My in-office Excel does not provide the XL2BB Add-in so I've created an example below. We're using Office 365.

This is the formula I'm using to count the number of times the error combo (helper) appears in the full historical data set. =COUNTIF(E:E,[@Helper])

The master data set is currently 4,874 rows and would continue to increase month-over-month by ~600.

I would prefer an in-cell formula vs. VBA macro if at all possible

Below is a sample of what the data looks like and the last column needs a formula that will look for the same Task Key, Exception and Issue Key combo (helper) counted only by consecutive months. Example: If exception was resolved in Aug-2023 but reappeared in Sep-2023, it would not include Jun-2023 and Jul-2023 in the # Months Unresolved. The count would start over beginning with Sep-2023.

Please let me know if more detail is needed. And thanks in advance for any guidance.

A	B	C	D	E	F
Month	Task Key	Exception	Issue Key	helper	# Months Unresolved
6/1/2023	ABC-1234567890	Overdue Issue	I543210	ABC-1234567890Overdue InvoiceI543210	1
7/1/2023	ABC-1234567890	Overdue Issue	I543210	ABC-1234567890Overdue InvoiceI543210	2
9/1/2023	ABC-1234567890	Overdue Issue	I543210	ABC-1234567890Overdue InvoiceI543210	1
10/1/2023	ABC-1234567890	Overdue Issue	I543210	ABC-1234567890Overdue InvoiceI543210	2
11/1/2023	ABC-1234567890	Overdue Issue	I543210	ABC-1234567890Overdue InvoiceI543210	3

 

[Peter_SSs]

My in-office Excel does not provide the XL2BB Add-in

The XL2BB Add-In is not provided by Excel, it is provided by this forum at the link in post 2 or the link in my signature block below.

Does this do what you want?

rhonllanders.xlsm
	A	B	C	D		F
1	Month	Task Key	Exception	Issue Key		# Months Unresolved
2	1/06/2023	ABC-1234567890	Overdue Invoice	I543210		1
3	1/07/2023	ABC-1234567890	Overdue Invoice	I543210		2
4	1/07/2023	XXX	Overdue Invoice	J22		1
5	1/08/2023	XXX	Overdue Invoice	J22		2
6	1/09/2023	ABC-1234567890	Overdue Invoice	I543210		1
7	1/09/2023	XXX	Overdue Invoice	J22		3
8	1/10/2023	ABC-1234567890	Overdue Invoice	I543210		2
9	1/11/2023	ABC-1234567890	Overdue Invoice	I543210		3
Sheet3

Cell Formulas
Range	Formula
F2:F9	F2	=LET(s,SEQUENCE(999),XLOOKUP(0,IFNA(MATCH(EDATE(A2,-s),FILTER(A$2:A2,(B$2:B2=B2)*(C$2:C2=C2)*(D$2:D2=D2)),0),0),s))