MOST FREQUENT RECURRING TEXT / PHRASE

[Unlucky]

Most frequent recurring text. I was able to enter the most frequent formula, but I'm having difficulty with the 2nd, 3rd, 4th, and 5th most frequent.

I use column D for a program name ($D$:$D$121)
I use column E for the recurring text I want to search for ($E$2:$E$121)
I use column F for the program name pool ($F$2:$F$14)

Column G ($G$2:$G$14) list the most frequent recurring text {=INDEX($E$2:$E$121,MODE(IF($D$2:$D$121=$F$2,MATCH($E$2:$E$121,$E$2:$E$121,0))))} . . . confirmed with Ctrl+Shift+Enter

Column H ($H$2:$H$14) is the 2nd most frequent recurring text
Column I ($I$2:$I$14) is the 3rd most frequent recurring text
Column J ($J$2:$J$14) is the 4th most frequent recurring text
Column K ($K$2:$K$14) is the 5th most frequent recurring text

Problem: I'm having trouble with entering the formula for columns H, I, J, & K

 

[Unlucky]

Keep in mind that the actual source data table is over 5000 Rows long, and it would take 20 to 30 minutes each time I wanted to update it by hand jamming it, which would be two to three times a day . . . and anytime my supervisor wanted an updated list; the list is a living document posted on a share point drive. I've worked on the "Open Documents" table and have all the formulas I need, except column A (Cells A4:A22); everything changes automatically (Columns C:P) when the numbers change (Column A). The number change is based on the "Source Data Table" document status (Cells K26:K59) (In actuality the source data is Column AN8:AN5258 on another spreadsheet).

Anyway, all I need is the formula for Column A to report the next number for 'open' documents listed in Column K of the source table. I already have the formula for the first 'document' (Cell A3) but have not been able to figure it out for the 2nd, 3rd, 4th, etc. (Cells A4:A22)) I tried using the formula provided for the original query above, but that appears to only work when reporting in a Row, vice a Column. In actuality, the report may be less than 20 documents, but rarely more than 20, again depending on whether the documents are 'open' or 'closed'.

Here is the reworked "Open Documents" table with the accompanying source table:

TEST LOG FOR FORMULAS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							OPEN	DOCUMENTS
2	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
3	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
4	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
5	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
6	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
7	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
8	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
9	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
10	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
11	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
12	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
13	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
14	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
15	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
16	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
17	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
18	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
19	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
20	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
21	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
22	2938120200008B		II	22-Sep-20	2-Oct-20		ABC	Bob			OPEN	Installed upside down
23
24	SOURCE DATA TABLE
25	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
26	2938120200005A		I	10-Sep-20	20-Sep-20	ABC		Tom			CLOSED	Loose hardware
27	2938120200006A		I	12-Sep-20	22-Sep-20	ABC		Charlie			CLOSED	Installed backwards
28	2938120200007A		I	12-Sep-20	22-Sep-20	CDR		William			CLOSED	Loose hardware
29	2938120200008A		I	21-Sep-20	1-Oct-20	QMF		William			CLOSED	Missing hardware
30	2938120200008B		II	22-Sep-20	2-Oct-20	ABC		Bob			OPEN	Installed upside down
31	2938120200008C		III	22-Sep-20	2-Oct-20	CRF		Tom			OPEN	Missing paint
32	2938120200009A		I	23-Sep-20	3-Oct-20	CDR		Greg			OPEN	Loose hardware
33	2938120200010A		I	4-Oct-20	14-Oct-20	QMF		Tom			CLOSED	Loose hardware
34	2938120200011A		I	5-Oct-20	15-Oct-20	WWW		William			CLOSED	Installed upside down
35	2938120200011B		II	12-Oct-20	22-Oct-20	WTF		Charlie			CLOSED	Installed backwards
36	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint
37	2938120200013A		I	3-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error
38	2938120200014B		II	6-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test
39	2938120200015A		I	6-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware
40	2938120200016A		I	7-Nov-20	17-Nov-20	WWW		William			CLOSED	Missing paint
41	2938120200016C		III	28-Nov-20	8-Dec-20	ILS		Charlie			OPEN	Installed backwards
42	2938120200017A		I	29-Nov-20	9-Dec-20	PRT		Charlie			CLOSED	Loose hardware
43	2938120200018A		I	8-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware
44	2938120200019A		I	10-Dec-20	20-Dec-20	CDR		Greg			CLOSED	Installed backwards
45	2938120200020A		I	15-Dec-20	25-Dec-20	CRF		Tom			CLOSED	Loose hardware
46	2938120200021A		I	22-Dec-20	1-Jan-21	CRF		William			CLOSED	Missing hardware
47	2938120200022B		II	30-Dec-20	9-Jan-21	CRF		Bob			CLOSED	Installed upside down
48	2938120200023A		I	30-Dec-20	9-Jan-21	WWW		Bob			CLOSED	Missing paint
49	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware
50	2938120200024A		I	7-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware
51	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down
52	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards
53	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint
54	2938120200027C		III	3-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware
55	2938120200028A		I	4-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down
56	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint
57	2938120200030B		II	8-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware
58	2938120200030C		III	9-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware
FORMULA TEST

Cell Formulas
Range	Formula
C3:C22	C3	=IFERROR(INDEX($C$26:$C$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
D3:D22	D3	=IFERROR(INDEX($D$26:$D$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
E3:E22	E3	=IFERROR(INDEX($E$26:$E$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
G3:G22	G3	=IFERROR(INDEX($F$26:$F$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
H3:H22	H3	=IFERROR(INDEX($H$26:$H$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
K3:K22	K3	=IFERROR(INDEX($K$26:$K$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
L3:L22	L3	=IFERROR(INDEX($L$26:$L$58, MATCH(0, IF($A3=$A$26:$A$59, COUNTIF($A$3:$A3, $G$1), ""), 0)),"")
A3:A22	A3	=IFERROR(INDEX($A$26:$A$58,MODE(IF(($K$26:$K$58=$G$1)*(COUNTIF($G$1,$A$26:$A$58)=0),MATCH($A$26:$A$58,$A$26:$A$58,0)*{1,1}))),"")
E26:E58	E26	=D26+10
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Unlucky]

Ignore this entry. When I first posted this morning (above), it did not appear, so I retyped it. Sorry.

 

[Eric W]

Your sample list simply consists of the same record repeated 19 times. I really can't discern much from that. Let me try a few guesses.

If you're just looking for a list of records with an OPEN status:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							OPEN	DOCUMENTS
2	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
3	2938120200008B		II	22-Sep-20	02-Oct-20	ABC		Bob			OPEN	Installed upside down	30
4	2938120200008C		III	22-Sep-20	02-Oct-20	CRF		Tom			OPEN	Missing paint	31
5	2938120200009A		I	23-Sep-20	03-Oct-20	CDR		Greg			OPEN	Loose hardware	32
6	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint	36
7	2938120200013A		I	03-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error	37
8	2938120200014B		II	06-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test	38
9	2938120200015A		I	06-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware	39
10	2938120200016C		III	28-Nov-20	08-Dec-20	ILS		Charlie			OPEN	Installed backwards	41
11	2938120200018A		I	08-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware	43
12	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware	49
13	2938120200024A		I	07-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware	50
14	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down	51
15	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards	52
16	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint	53
17	2938120200027C		III	03-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware	54
18	2938120200028A		I	04-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down	55
19	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint	56
20	2938120200030B		II	08-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware	57
21	2938120200030C		III	09-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware	58
22
23
Sheet4

Cell Formulas
Range	Formula
C3:F22,H3:H22,A3:A22,K3:L22	C3	=IF($M3="","",INDEX(C:C,$M3))
M3:M23	M3	=IFERROR(SMALL(IF($K$26:$K$58=$G$1,ROW($K$26:$K$58)),ROWS($M$3:$M3)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

I used a helper column in column M to avoid using a lot of array formulas, which can slow down the sheet. If you want to avoid the helper column, and if the number in column A is unique, then try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1							OPEN	DOCUMENTS
2	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
3	2938120200008B		II	22-Sep-20	02-Oct-20	ABC		Bob			OPEN	Installed upside down
4	2938120200008C		III	22-Sep-20	02-Oct-20	CRF		Tom			OPEN	Missing paint
5	2938120200009A		I	23-Sep-20	03-Oct-20	CDR		Greg			OPEN	Loose hardware
6	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint
7	2938120200013A		I	03-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error
8	2938120200014B		II	06-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test
9	2938120200015A		I	06-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware
10	2938120200016C		III	28-Nov-20	08-Dec-20	ILS		Charlie			OPEN	Installed backwards
11	2938120200018A		I	08-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware
12	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware
13	2938120200024A		I	07-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware
14	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down
15	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards
16	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint
17	2938120200027C		III	03-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware
18	2938120200028A		I	04-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down
19	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint
20	2938120200030B		II	08-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware
21	2938120200030C		III	09-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware
22
Sheet4

Cell Formulas
Range	Formula
C3:F22,H3:H22,K3:L22	C3	=IF($A3="","",VLOOKUP($A3,$A$26:$L$58,COLUMNS($A3:C3),0))
A3:A22	A3	=IFERROR(INDEX($A$26:$A$58,SMALL(IF($K$26:$K$58=$G$1,ROW($K$26:$K$58)-ROW($K$26)+1),ROWS($A$3:$A3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

And if you want the list of open items, sorted by frequency of comments, try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							OPEN	DOCUMENTS
2	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
3	2938120200030C		III	09-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware	7.0058
4	2938120200030B		II	08-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware	7.0057
5	2938120200024A		I	07-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware	7.005
6	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware	7.0049
7	2938120200018A		I	08-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware	7.0043
8	2938120200015A		I	06-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware	7.0039
9	2938120200009A		I	23-Sep-20	03-Oct-20	CDR		Greg			OPEN	Loose hardware	7.0032
10	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint	4.0056
11	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint	4.0053
12	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint	4.0036
13	2938120200008C		III	22-Sep-20	02-Oct-20	CRF		Tom			OPEN	Missing paint	4.0031
14	2938120200028A		I	04-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down	3.0055
15	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down	3.0051
16	2938120200008B		II	22-Sep-20	02-Oct-20	ABC		Bob			OPEN	Installed upside down	3.003
17	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards	2.0052
18	2938120200016C		III	28-Nov-20	08-Dec-20	ILS		Charlie			OPEN	Installed backwards	2.0041
19	2938120200027C		III	03-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware	1.0054
20	2938120200014B		II	06-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test	1.0038
21	2938120200013A		I	03-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error	1.0037
22
Sheet4

Cell Formulas
Range	Formula
C3:F21,H3:H21,A3:A22,K3:L21	C3	=IF($M3="","",INDEX(C:C,MOD($M3,1)*10000))
C22:F22,K22:L22,H22	C22	=IF($M22="","",INDEX(C:C,MOD($M22,1)*1000))
M3:M21	M3	=IFERROR(LARGE(IF($K$26:$K$58=$G$1,COUNTIFS($K$26:$K$58,$G$1,$L$26:$L$58,$L$26:$L$58)+ROW($L$26:$L$58)/10000),ROWS($M$3:$M3)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

I can get rid of the helper column here too if you like.

 

[Unlucky]

That did it! I used the second option and, as always, you were spot on with a perfect formula! And again, as always, thank you very much! It is genuinely appreciated. ?

 

[Eric W]

Happy to help! ?

 

[Unlucky]

I made two 'reserve' tables with the other two formulas, . . . . . just in case the one I used doesn't work as well as planned; always have a back-up plan! Again, thanks for your help, you're awesome!

? ? ?

 

[Eric W]

It's always nice to have options! I thought of a minor issue with the 3rd formula. Here's a fixed version if you ever need it:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							OPEN	DOCUMENTS
2	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
3	2938120200030C		III	09-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware	7001.006
4	2938120200030B		II	08-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware	7001.006
5	2938120200024A		I	07-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware	7001.005
6	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware	7001.005
7	2938120200018A		I	08-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware	7001.004
8	2938120200015A		I	06-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware	7001.004
9	2938120200009A		I	23-Sep-20	03-Oct-20	CDR		Greg			OPEN	Loose hardware	7001.003
10	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint	4006.006
11	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint	4006.005
12	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint	4006.004
13	2938120200008C		III	22-Sep-20	02-Oct-20	CRF		Tom			OPEN	Missing paint	4006.003
14	2938120200028A		I	04-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down	4005.006
15	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down	4005.005
16	2938120200016C		III	28-Nov-20	08-Dec-20	ILS		Charlie			OPEN	Installed upside down	4005.004
17	2938120200008B		II	22-Sep-20	02-Oct-20	ABC		Bob			OPEN	Installed upside down	4005.003
18	2938120200014B		II	06-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test	1013.004
19	2938120200013A		I	03-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error	1012.004
20	2938120200027C		III	03-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware	1004.005
21	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards	1002.005
22
23
24	SOURCE DATA TABLE
25	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
26	2938120200005A		I	10-Sep-20	20-Sep-20	ABC		Tom			CLOSED	Loose hardware
27	2938120200006A		I	12-Sep-20	22-Sep-20	ABC		Charlie			CLOSED	Installed backwards
28	2938120200007A		I	12-Sep-20	22-Sep-20	CDR		William			CLOSED	Loose hardware
29	2938120200008A		I	21-Sep-20	01-Oct-20	QMF		William			CLOSED	Missing hardware
30	2938120200008B		II	22-Sep-20	02-Oct-20	ABC		Bob			OPEN	Installed upside down
31	2938120200008C		III	22-Sep-20	02-Oct-20	CRF		Tom			OPEN	Missing paint
32	2938120200009A		I	23-Sep-20	03-Oct-20	CDR		Greg			OPEN	Loose hardware
33	2938120200010A		I	04-Oct-20	14-Oct-20	QMF		Tom			CLOSED	Loose hardware
34	2938120200011A		I	05-Oct-20	15-Oct-20	WWW		William			CLOSED	Installed upside down
35	2938120200011B		II	12-Oct-20	22-Oct-20	WTF		Charlie			CLOSED	Installed upside down
36	2938120200012A		I	14-Oct-20	24-Oct-20	CDR		Bob			OPEN	Missing paint
37	2938120200013A		I	03-Nov-20	13-Nov-20	ABC		Bob			OPEN	Operator error
38	2938120200014B		II	06-Nov-20	16-Nov-20	ABC		Bob			OPEN	Failed test
39	2938120200015A		I	06-Nov-20	16-Nov-20	WTF		Tom			OPEN	Loose hardware
40	2938120200016A		I	07-Nov-20	17-Nov-20	WWW		William			CLOSED	Missing paint
41	2938120200016C		III	28-Nov-20	08-Dec-20	ILS		Charlie			OPEN	Installed upside down
42	2938120200017A		I	29-Nov-20	09-Dec-20	PRT		Charlie			CLOSED	Loose hardware
43	2938120200018A		I	08-Dec-20	18-Dec-20	ABC		Bob			OPEN	Loose hardware
44	2938120200019A		I	10-Dec-20	20-Dec-20	CDR		Greg			CLOSED	Installed backwards
45	2938120200020A		I	15-Dec-20	25-Dec-20	CRF		Tom			CLOSED	Loose hardware
46	2938120200021A		I	22-Dec-20	01-Jan-21	CRF		William			CLOSED	Missing hardware
47	2938120200022B		II	30-Dec-20	09-Jan-21	CRF		Bob			CLOSED	Installed upside down
48	2938120200023A		I	30-Dec-20	09-Jan-21	WWW		Bob			CLOSED	Missing paint
49	2938120200023B		II	31-Dec-20	10-Jan-21	ABC		William			OPEN	Loose hardware
50	2938120200024A		I	07-Jan-21	17-Jan-21	ILF		Greg			OPEN	Loose hardware
51	2938120200025A		I	12-Jan-21	22-Jan-21	WTF		Tom			OPEN	Installed upside down
52	2938120200026A		I	13-Jan-21	23-Jan-21	ABC		Tom			OPEN	Installed backwards
53	2938120200027A		I	21-Jan-21	31-Jan-21	ABC		Tom			OPEN	Missing paint
54	2938120200027C		III	03-Feb-21	13-Feb-21	ABC		Tom			OPEN	Missing hardware
55	2938120200028A		I	04-Feb-21	14-Feb-21	WTF		Greg			OPEN	Installed upside down
56	2938120200029A		I	13-Feb-21	23-Feb-21	WWW		William			OPEN	Missing paint
57	2938120200030B		II	08-Mar-21	18-Mar-21	ILS		Bob			OPEN	Loose hardware
58	2938120200030C		III	09-Mar-21	19-Mar-21	PCF		Tom			OPEN	Loose hardware
59
Sheet4

Cell Formulas
Range	Formula
C3:F21,H3:H21,A3:A22,K3:L21	C3	=IF($M3="","",INDEX(C:C,MOD($M3,1)*10000))
C22:F22,K22:L22,H22	C22	=IF($M22="","",INDEX(C:C,MOD($M22,1)*1000))
M3:M21	M3	=IFERROR(LARGE(IF($K$26:$K$58=$G$1,COUNTIFS($K$26:$K$58,$G$1,$L$26:$L$58,$L$26:$L$58)*1000+MATCH($L$26:$L$58,$L$26:$L$58,0)+ROW($L$26:$L$58)/10000),ROWS($M$3:$M3)),"")
E26:E58	E26	=D26+10

Glad I could help!

 

[Unlucky]

Understood. Thanks again! You've been an enormous help!