Counting cells between matching text

[Learden]

Hello, I searched previous threads and did not find quite what I was looking for. I have a list of analyst initials that are assigned a task on a rotating basis. They would like to know the minimum number of weeks between each assignment so I would like to count the number of cells between two matching references (for example "LM") through out the year and have it return the minimum number of weeks between two appearances of "LM". I am trying to attach a mini sheet using Xl2bb, and it is my first time so I apologize if it is incorrect. It looks really messy.

00. MASTER BATCHING SCHEDULE.xlsm
	A	B	C
1
2		Tuesday	Thursday
3	WEEK OF:	Direct Amp BR
4	Dec. 27#	JP
5	Jan. 3	GRF	CAC
6	Jan. 10	LM	GMH
7	Jan. 17*	JP	MDS
8	Jan. 24	GRF	CAC
9	Jan. 31	TFS	LM
10	Feb. 7	JP	MDS
11	Feb. 14	GRF	CAC
12	Feb. 21*	GMH	GMH
13	Feb 28	TFS	LM
14	Mar. 7	GMH	CAC
15	Mar. 14	TFS	JP
16	Mar. 21	CAC	MDS
17	Mar. 28*	GRF	SLC
18	Apr. 4	TFS	JP
19	Apr. 11	SLC	LM
20	Apr. 18	GRF	CAC
21	Apr. 25	MDS	SLC
22	May 2	GRF	LM
23	May 9	JP	MDS
2022

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:C31,B56:C56	Cell Value	="jgl"	text	NO
B5:C30,B4	Cell Value	contains "JGL"	text	NO
B6	Cell Value	contains "JGL"	text	NO

 

[KRice]

Give this a try to see if it does what you want. The value returned for "Min Between" represents the number of intervals based on row counting.

MrExcel_20220426.xlsm
	A	B	C	D	E	F
3	WEEK OF:	Direct Amp BR			Initials	Min Between
4	Dec. 27#	JP			CAC	2
5	Jan. 3	GRF	CAC		GMH	2
6	Jan. 10	LM	GMH		GRF	2
7	Jan. 17*	JP	MDS		JP	3
8	Jan. 24	GRF	CAC		LM	3
9	Jan. 31	TFS	LM		MDS	2
10	Feb. 7	JP	MDS		SLC	2
11	Feb. 14	GRF	CAC		TFS	2
12	Feb. 21*	GMH	GMH
13	28-Feb	TFS	LM
14	Mar. 7	GMH	CAC
15	Mar. 14	TFS	JP
16	Mar. 21	CAC	MDS
17	Mar. 28*	GRF	SLC
18	Apr. 4	TFS	JP
19	Apr. 11	SLC	LM
20	Apr. 18	GRF	CAC
21	Apr. 25	MDS	SLC
22	2-May	GRF	LM
23	9-May	JP	MDS
Sheet3

Cell Formulas
Range	Formula
E4:E11	E4	=SORT(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,B4:C23),"|","</b><b>")&"</b></a>","//b")))
F4:F11	F4	=LET(r,FILTER(SEQUENCE(ROWS($A$4:$A$23)),($B$4:$B$23=E4)+($C$4:$C$23=E4)),diffr,INDEX(r,SEQUENCE(ROWS(r)-1,,2))-INDEX(r,SEQUENCE(ROWS(r)-1,,1)),MIN(diffr))
Dynamic array formulas.

 

[KRice]

And a slight modification will let you see all of the row intervals in cols G:M.

MrExcel_20220426.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Tuesday	Thursday
3	WEEK OF:	Direct Amp BR			Initials	Min Between	CAC	GMH	GRF	JP	LM	MDS	SLC	TFS
4	Dec. 27#	JP			CAC	2	3	6	3	3	3	3	2	4
5	Jan. 3	GRF	CAC		GMH	2	3	2	3	3	4	6	2	2
6	Jan. 10	LM	GMH		GRF	2	3		6	5	6	5		3
7	Jan. 17*	JP	MDS		JP	3	2		3	3	3	2
8	Jan. 24	GRF	CAC		LM	3	4		2	5
9	Jan. 31	TFS	LM		MDS	2
10	Feb. 7	JP	MDS		SLC	2
11	Feb. 14	GRF	CAC		TFS	2
12	Feb. 21*	GMH	GMH
13	28-Feb	TFS	LM
14	Mar. 7	GMH	CAC
15	Mar. 14	TFS	JP
16	Mar. 21	CAC	MDS
17	Mar. 28*	GRF	SLC
18	Apr. 4	TFS	JP
19	Apr. 11	SLC	LM
20	Apr. 18	GRF	CAC
21	Apr. 25	MDS	SLC
22	2-May	GRF	LM
23	9-May	JP	MDS
Sheet3

Cell Formulas
Range	Formula
G3:N3	G3	=TRANSPOSE(E4#)
E4:E11	E4	=SORT(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,B4:C23),"|","</b><b>")&"</b></a>","//b")))
G4:G8,N4:N6,M4:M5,K4:L7,I4:J8,H4:H5	G4	=LET(r,FILTER(SEQUENCE(ROWS($A$4:$A$23)),($B$4:$B$23=G$3)+($C$4:$C$23=G$3)),diffr,INDEX(r,SEQUENCE(ROWS(r)-1,,2))-INDEX(r,SEQUENCE(ROWS(r)-1,,1)),diffr)
F4:F11	F4	=LET(r,FILTER(SEQUENCE(ROWS($A$4:$A$23)),($B$4:$B$23=E4)+($C$4:$C$23=E4)),diffr,INDEX(r,SEQUENCE(ROWS(r)-1,,2))-INDEX(r,SEQUENCE(ROWS(r)-1,,1)),MIN(diffr))
Dynamic array formulas.

 

[Learden]

And a slight modification will let you see all of the row intervals in cols G:M.

MrExcel_20220426.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Tuesday	Thursday
3	WEEK OF:	Direct Amp BR			Initials	Min Between	CAC	GMH	GRF	JP	LM	MDS	SLC	TFS
4	Dec. 27#	JP			CAC	2	3	6	3	3	3	3	2	4
5	Jan. 3	GRF	CAC		GMH	2	3	2	3	3	4	6	2	2
6	Jan. 10	LM	GMH		GRF	2	3		6	5	6	5		3
7	Jan. 17*	JP	MDS		JP	3	2		3	3	3	2
8	Jan. 24	GRF	CAC		LM	3	4		2	5
9	Jan. 31	TFS	LM		MDS	2
10	Feb. 7	JP	MDS		SLC	2
11	Feb. 14	GRF	CAC		TFS	2
12	Feb. 21*	GMH	GMH
13	28-Feb	TFS	LM
14	Mar. 7	GMH	CAC
15	Mar. 14	TFS	JP
16	Mar. 21	CAC	MDS
17	Mar. 28*	GRF	SLC
18	Apr. 4	TFS	JP
19	Apr. 11	SLC	LM
20	Apr. 18	GRF	CAC
21	Apr. 25	MDS	SLC
22	2-May	GRF	LM
23	9-May	JP	MDS
Sheet3

Cell Formulas
Range	Formula
G3:N3	G3	=TRANSPOSE(E4#)
E4:E11	E4	=SORT(UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("|",TRUE,B4:C23),"|","</b><b>")&"</b></a>","//b")))
G4:G8,N4:N6,M4:M5,K4:L7,I4:J8,H4:H5	G4	=LET(r,FILTER(SEQUENCE(ROWS($A$4:$A$23)),($B$4:$B$23=G$3)+($C$4:$C$23=G$3)),diffr,INDEX(r,SEQUENCE(ROWS(r)-1,,2))-INDEX(r,SEQUENCE(ROWS(r)-1,,1)),diffr)
F4:F11	F4	=LET(r,FILTER(SEQUENCE(ROWS($A$4:$A$23)),($B$4:$B$23=E4)+($C$4:$C$23=E4)),diffr,INDEX(r,SEQUENCE(ROWS(r)-1,,2))-INDEX(r,SEQUENCE(ROWS(r)-1,,1)),MIN(diffr))
Dynamic array formulas.

Thank you very much. I would never have been able to sort that out.

 

[KRice]

You’re welcome…I’m happy to help.