How do I create a summary report with data from a single tab?

[VA_Exceluser]

I'm a new Excel Report creator. I have a single workbook with one tab containing 967 rows of data. I'd like to create a summary tab with each person's name and site_name appearing once, list of CI Dates separated by commas, the total number of times each person did testing (sum of CI Dates, which may be just 1 or as many as 12), and a list of the products tested separated by commas.

A snippet of the data is as follows:

Thank You Report--for testing.xlsx
	A	B	C	D
1	Name	CI Date	Product Tested	Site_Name
2	Abie Smith	06/11/2023	Document Center	Chicago
3	Addison Farmer	10/9/2022	Document Center	New York
4	Addison Farmer	12/11/2022	Document Center	New York
5	Addison Farmer	02/12/2023	Document Center	New York
6	Addison Farmer	04/23/2023	Document Center	New York
7	Addison Farmer	08/20/2023	Document Center	New York
8	Albert Smith	07/09/2023	Layer 1	Ann Arbor
9	Albert Smith	09/11/2022	Layer 1	Ann Arbor
10	Albert Smith	01/15/2023	Layer 3	Ann Arbor
11	Albert Smith	02/12/2023	Layer 3	Ann Arbor
12	Albert Smith	03/12/2023	Layer 1	Ann Arbor
13	Roger Smith	04/23/2023	Pakmanager	New York
14	Roger Smith	05/21/2023	Paknstore	New York
15	Roger Smith	06/11/2023	Paknstore	New York
16	Roger Smith	08/20/2023	Paknstore	New York
17	Samuel Simms	07/09/2023	VBLM	New Jersey
18	Samuel Simms	10/9/2022	VBLM	New Jersey
19	Samuel Simms	12/11/2022	VBLM-2	New Jersey
Data

Thanks.

--Bill

 

[etaf]

ignore = i did overcomplicate the formula
@AhoyNC has a much better solution

 

[AhoyNC]

Maybe something like this:

Book4
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 11/12/22, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	11/12/2022	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor
13	Roger Smith	4/23/2023	Pakmanager	New York
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
Sheet1

Cell Formulas
Range	Formula
F2:G6	F2	=UNIQUE(FILTER($A$2:$D$19,{1,0,0,1}))
H2:H6	H2	=COUNT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
I2:I6	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)),"m/dd/yy"))
J2:J6	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
Dynamic array formulas.

 

[VA_Exceluser]

Maybe something like this:

Book4
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 11/12/22, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	11/12/2022	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor
13	Roger Smith	4/23/2023	Pakmanager	New York
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
Sheet1

Cell Formulas
Range	Formula
F2:G6	F2	=UNIQUE(FILTER($A$2:$D$19,{1,0,0,1}))
H2:H6	H2	=COUNT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
I2:I6	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)),"m/dd/yy"))
J2:J6	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
Dynamic array formulas.

Maybe something like this:

Book4
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 11/12/22, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	11/12/2022	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor
13	Roger Smith	4/23/2023	Pakmanager	New York
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
Sheet1

Cell Formulas
Range	Formula
F2:G6	F2	=UNIQUE(FILTER($A$2:$D$19,{1,0,0,1}))
H2:H6	H2	=COUNT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
I2:I6	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)),"m/dd/yy"))
J2:J6	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))
Dynamic array formulas.

=COUNT(FILTER($B$2:$B$19,(F2=$A$2:$A$19)*($D$2:$D$19=G2)))

Thanks. The formulas work great until I get past row 19. I have 868 rows of data, Aside from the first formula, in which I change "19" to "868", I wasn't able to extend the other formulas for all the data. I tried replacing 19 in the other formulas, but I just got empty array errors. Can you explain how to extend your formulas to cover rows 20 to 868?

--Bill

 

[AhoyNC]

You should be able to change the 19 to 868. See example below. I only added data to row 67 which is why there are 0's in F13 and G13.

Book2
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 11/12/22, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	11/12/2022	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York		Name1	Chicago	8	6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23	Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor		Name2	Chicago	8	9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22	Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor		Name3	Chicago	8	11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23	Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor		Name4	Chicago	8	2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23	Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor		Name5	Chicago	8	4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23	Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor		Name6	Chicago	8	8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23	Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center
13	Roger Smith	4/23/2023	Pakmanager	New York		0	0
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
20	Name1	6/11/2023	Document Center	Chicago
21	Name2	9/10/2022	Document Center	Chicago
22	Name3	11/12/2022	Document Center	Chicago
23	Name4	2/12/2023	Document Center	Chicago
24	Name5	4/23/2023	Pakmanager	Chicago
25	Name6	8/20/2023	Paknstore	Chicago
26	Name1	9/7/2023	Paknstore	Chicago
27	Name2	9/11/2022	Paknstore	Chicago
28	Name3	1/15/2023	VBLM	Chicago
29	Name4	2/12/2023	VBLM	Chicago
30	Name5	3/12/2023	VBLM-2	Chicago
31	Name6	4/23/2023	Document Center	Chicago
32	Name1	5/21/2023	Document Center	Chicago
33	Name2	6/11/2023	Document Center	Chicago
34	Name3	8/20/2023	Document Center	Chicago
35	Name4	9/7/2023	Pakmanager	Chicago
36	Name5	9/10/2022	Paknstore	Chicago
37	Name6	11/12/2022	Paknstore	Chicago
38	Name1	6/11/2023	Paknstore	Chicago
39	Name2	9/10/2022	VBLM	Chicago
40	Name3	11/12/2022	VBLM	Chicago
41	Name4	2/12/2023	VBLM-2	Chicago
42	Name5	4/23/2023	Document Center	Chicago
43	Name6	8/20/2023	Document Center	Chicago
44	Name1	9/7/2023	Document Center	Chicago
45	Name2	9/11/2022	Document Center	Chicago
46	Name3	1/15/2023	Pakmanager	Chicago
47	Name4	2/12/2023	Paknstore	Chicago
48	Name5	3/12/2023	Paknstore	Chicago
49	Name6	4/23/2023	Paknstore	Chicago
50	Name1	5/21/2023	VBLM	Chicago
51	Name2	6/11/2023	VBLM	Chicago
52	Name3	8/20/2023	VBLM-2	Chicago
53	Name4	9/7/2023	Document Center	Chicago
54	Name5	9/10/2022	Document Center	Chicago
55	Name6	11/12/2022	Document Center	Chicago
56	Name1	6/11/2023	Document Center	Chicago
57	Name2	9/10/2022	Pakmanager	Chicago
58	Name3	11/12/2022	Paknstore	Chicago
59	Name4	2/12/2023	Paknstore	Chicago
60	Name5	4/23/2023	Paknstore	Chicago
61	Name6	8/20/2023	VBLM	Chicago
62	Name1	9/7/2023	VBLM	Chicago
63	Name2	9/11/2022	VBLM-2	Chicago
64	Name3	1/15/2023	Document Center	Chicago
65	Name4	2/12/2023	Document Center	Chicago
66	Name5	3/12/2023	Document Center	Chicago
67	Name6	4/23/2023	Document Center	Chicago
Sheet1

Cell Formulas
Range	Formula
F2:G13	F2	=UNIQUE(FILTER($A$2:$D$868,{1,0,0,1}))
H2:H12	H2	=COUNT(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
I2:I12	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)),"m/dd/yy"))
J2:J12	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
Dynamic array formulas.

 

[VA_Exceluser]

You should be able to change the 19 to 868. See example below. I only added data to row 67 which is why there are 0's in F13 and G13.

Book2
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 11/12/22, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	11/12/2022	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York		Name1	Chicago	8	6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23	Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor		Name2	Chicago	8	9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22	Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor		Name3	Chicago	8	11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23	Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor		Name4	Chicago	8	2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23	Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor		Name5	Chicago	8	4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23	Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor		Name6	Chicago	8	8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23	Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center
13	Roger Smith	4/23/2023	Pakmanager	New York		0	0
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
20	Name1	6/11/2023	Document Center	Chicago
21	Name2	9/10/2022	Document Center	Chicago
22	Name3	11/12/2022	Document Center	Chicago
23	Name4	2/12/2023	Document Center	Chicago
24	Name5	4/23/2023	Pakmanager	Chicago
25	Name6	8/20/2023	Paknstore	Chicago
26	Name1	9/7/2023	Paknstore	Chicago
27	Name2	9/11/2022	Paknstore	Chicago
28	Name3	1/15/2023	VBLM	Chicago
29	Name4	2/12/2023	VBLM	Chicago
30	Name5	3/12/2023	VBLM-2	Chicago
31	Name6	4/23/2023	Document Center	Chicago
32	Name1	5/21/2023	Document Center	Chicago
33	Name2	6/11/2023	Document Center	Chicago
34	Name3	8/20/2023	Document Center	Chicago
35	Name4	9/7/2023	Pakmanager	Chicago
36	Name5	9/10/2022	Paknstore	Chicago
37	Name6	11/12/2022	Paknstore	Chicago
38	Name1	6/11/2023	Paknstore	Chicago
39	Name2	9/10/2022	VBLM	Chicago
40	Name3	11/12/2022	VBLM	Chicago
41	Name4	2/12/2023	VBLM-2	Chicago
42	Name5	4/23/2023	Document Center	Chicago
43	Name6	8/20/2023	Document Center	Chicago
44	Name1	9/7/2023	Document Center	Chicago
45	Name2	9/11/2022	Document Center	Chicago
46	Name3	1/15/2023	Pakmanager	Chicago
47	Name4	2/12/2023	Paknstore	Chicago
48	Name5	3/12/2023	Paknstore	Chicago
49	Name6	4/23/2023	Paknstore	Chicago
50	Name1	5/21/2023	VBLM	Chicago
51	Name2	6/11/2023	VBLM	Chicago
52	Name3	8/20/2023	VBLM-2	Chicago
53	Name4	9/7/2023	Document Center	Chicago
54	Name5	9/10/2022	Document Center	Chicago
55	Name6	11/12/2022	Document Center	Chicago
56	Name1	6/11/2023	Document Center	Chicago
57	Name2	9/10/2022	Pakmanager	Chicago
58	Name3	11/12/2022	Paknstore	Chicago
59	Name4	2/12/2023	Paknstore	Chicago
60	Name5	4/23/2023	Paknstore	Chicago
61	Name6	8/20/2023	VBLM	Chicago
62	Name1	9/7/2023	VBLM	Chicago
63	Name2	9/11/2022	VBLM-2	Chicago
64	Name3	1/15/2023	Document Center	Chicago
65	Name4	2/12/2023	Document Center	Chicago
66	Name5	3/12/2023	Document Center	Chicago
67	Name6	4/23/2023	Document Center	Chicago
Sheet1

Cell Formulas
Range	Formula
F2:G13	F2	=UNIQUE(FILTER($A$2:$D$868,{1,0,0,1}))
H2:H12	H2	=COUNT(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
I2:I12	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)),"m/dd/yy"))
J2:J12	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
Dynamic array formulas.

The formulas work great except in intermittent rows, the number of tests appears as 0 even though the person took part in 1 test. The second and third row, for example show 0 tests. I used the following formula for those rows:

=COUNT(FILTER($B$2:$B$868,(F3=$A$2:$A$868)*($D$2:$D$868=G3)))
=COUNT(FILTER($B$2:$B$868,(F4=$A$2:$A$868)*($D$2:$D$868=G4)))

346 - Seattle	1	6/11/23
330 - Milwaukee	0	10/09/22
317 - St. Petersburg	0	12/11/22
377 - San Diego	3	2/12/23, 4/23/23, 8/20/23, 7/09/23

Any suggestions?

Thanks.

 

[AhoyNC]

Not sure what you mean by "intermittent rows".

What happens if you change COUNT to ROWS?

=ROWS(FILTER($B$2:$B$868,(F3=$A$2:$A$868)*($D$2:$D$868=G3)))

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	CI Date	Product Tested	Site_Name		Name	Site_Name	# Tests	Dates	Tests
2	Abie Smith	6/11/2023	Document Center	Chicago		Abie Smith	Chicago	1	6/11/23	Document Center
3	Addison Farmer	9/10/2022	Document Center	New York		Addison Farmer	New York	5	9/10/22, 1/00/00, 2/12/23, 4/23/23, 8/20/23	Document Center, Document Center, Document Center, Document Center, Document Center
4	Addison Farmer	1/0/1900	Document Center	New York		Albert Smith	Ann Arbor	5	9/07/23, 9/11/22, 1/15/23, 2/12/23, 3/12/23	Layer 1, Layer 1, Layer 3, Layer 3, Layer 1
5	Addison Farmer	2/12/2023	Document Center	New York		Roger Smith	New York	4	4/23/23, 5/21/23, 6/11/23, 8/20/23	Pakmanager, Paknstore, Paknstore, Paknstore
6	Addison Farmer	4/23/2023	Document Center	New York		Samuel Simms	New Jersey	3	9/07/23, 9/10/22, 11/12/22	VBLM, VBLM, VBLM-2
7	Addison Farmer	8/20/2023	Document Center	New York		Name1	Chicago	8	6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23, 5/21/23, 6/11/23, 9/07/23	Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM
8	Albert Smith	9/7/2023	Layer 1	Ann Arbor		Name2	Chicago	8	9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22, 6/11/23, 9/10/22, 9/11/22	Document Center, Paknstore, Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2
9	Albert Smith	9/11/2022	Layer 1	Ann Arbor		Name3	Chicago	8	11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23, 8/20/23, 11/12/22, 1/15/23	Document Center, VBLM, Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center
10	Albert Smith	1/15/2023	Layer 3	Ann Arbor		Name4	Chicago	8	2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23, 9/07/23, 2/12/23, 2/12/23	Document Center, VBLM, Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center
11	Albert Smith	2/12/2023	Layer 3	Ann Arbor		Name5	Chicago	8	4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23, 9/10/22, 4/23/23, 3/12/23	Pakmanager, VBLM-2, Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center
12	Albert Smith	3/12/2023	Layer 1	Ann Arbor		Name6	Chicago	8	8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23, 11/12/22, 8/20/23, 4/23/23	Paknstore, Document Center, Paknstore, Document Center, Paknstore, Document Center, VBLM, Document Center
13	Roger Smith	4/23/2023	Pakmanager	New York		0	0
14	Roger Smith	5/21/2023	Paknstore	New York
15	Roger Smith	6/11/2023	Paknstore	New York
16	Roger Smith	8/20/2023	Paknstore	New York
17	Samuel Simms	9/7/2023	VBLM	New Jersey
18	Samuel Simms	9/10/2022	VBLM	New Jersey
19	Samuel Simms	11/12/2022	VBLM-2	New Jersey
20	Name1	6/11/2023	Document Center	Chicago
21	Name2	9/10/2022	Document Center	Chicago
22	Name3	11/12/2022	Document Center	Chicago
23	Name4	2/12/2023	Document Center	Chicago
66	Name5	3/12/2023	Document Center	Chicago
67	Name6	4/23/2023	Document Center	Chicago
Sheet1

Cell Formulas
Range	Formula
F2:G13	F2	=UNIQUE(FILTER($A$2:$D$868,{1,0,0,1}))
H2:H12	H2	=ROWS(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
I2:I12	I2	=TEXTJOIN(", ",1,TEXT(FILTER($B$2:$B$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)),"m/dd/yy"))
J2:J12	J2	=TEXTJOIN(", ",1,FILTER($C$2:$C$868,(F2=$A$2:$A$868)*($D$2:$D$868=G2)))
Dynamic array formulas.