Upgrade assist to a formula

[robbarba]

Good Morning,
Last year our client asked that we randomly select two work ticket to audit, and the people of the page responded ! Now they want to increase the count of Audits to at least 3 or at most 5 here is what we have for getting 2 random audits. how can change it to do up to 5, at least 3

2023 03 TechAudit v1.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Incident ID*+	First Name+	Last Name+	FULL NAME	Submit Date	Summary*	Incident Type*	Assigned Group*+	Assignee+	Status*	Last Resolved Date		Technician	Audited Inncident
3	INC000004174281	Geoffery	Hewlett	Geoffery Hewlett	1/31/2023 7:47:23 PM	Laptop Request IMACD 20230221	User Service Request	IMACD Onboarding	Orlando A Roberts	Closed	3/1/2023 6:53:27 AM		Alexander Koutroubas	INC000004230606
4	INC000004192652	Michael	Martin	Michael Martin	2/14/2023 6:37:14 AM	Laptop Request IMACD 20230221	User Service Request	IMACD Onboarding	Orlando A Roberts	Closed	3/1/2023 6:54:31 AM		Alexander Koutroubas	INC000004235776
5	INC000004169215	James	Esquivel	James Esquivel	1/27/2023 12:15:26 PM	Product Ordering for :Monitor	User Service Request	IMACD	Christopher G Cabotaje	Closed	3/1/2023 7:41:40 AM		Alfonso Phillips	INC000004243216
6	INC000004206992	Justin	Surprenant	Justin Surprenant	2/23/2023 10:45:31 AM	CMS - Win7 - Re-image and rebuild CMS - Foreman role	User Service Restoration	FTA Support	Erick Gonzalez	Closed	3/1/2023 7:50:57 AM		Alfonso Phillips	INC000004245200
7	INC000004193290	Kumaraswamy	Purumala	Kumaraswamy Purumala	2/14/2023 12:22:43 PM	Product Ordering for :Laptop	User Service Request	IMACD Onboarding	Sudiksha Niroula	Closed	3/1/2023 7:53:26 AM		Armando Gonzales	INC000004230886
8	INC000004193678	Romana	Samad	Romana Samad	2/14/2023 3:09:37 PM	Surface Laptop: Unable to connect to Hotspot	User Service Restoration	IMACD	Sudiksha Niroula	Closed	3/1/2023 8:03:37 AM		Armando Gonzales	INC000004242900
9	INC000004199235	Marian	Lafferty	Marian Lafferty	2/17/2023 11:14:29 AM	TechDay - Innovation Village 3	User Service Request	IMACD	Miguel Cisneros	Closed	3/1/2023 8:05:14 AM		Bruce Bishara	INC000004197611
10	INC000004179453	David	Flores	David Flores	2/3/2023 10:02:22 AM	Report an issue : The battery is not working (this battery was a replacement)	User Service Restoration	IMACD	Sudiksha Niroula	Closed	3/1/2023 8:08:37 AM		Bruce Bishara	INC000004203057
11	INC000004195410	Todd	Gallaher	Todd Gallaher	2/15/2023 12:45:03 PM	Report an Issue - While using Microsoft Outlook I receive an error message (see attachment). Additio	User Service Restoration	IMACD	Bruce Bishara	Closed	3/1/2023 8:18:30 AM		Carolina Preciado	INC000004123313
12	INC000004193378	Adam	Wires	Adam Wires	2/14/2023 11:31:07 AM	Device Return : Laptop;Monitor;PC Peripherals	User Service Request	IMACD - Off Boarding	Christopher G Cabotaje	Closed	3/1/2023 8:18:44 AM		Carolina Preciado	INC000004149497
13	INC000004194891	Angelica	Pichay	Angelica Pichay	2/15/2023 10:41:56 AM	Hardware - Monitor Issue	User Service Restoration	IMACD	Sudiksha Niroula	Closed	3/1/2023 8:32:58 AM		Christopher G Cabotaje	INC000004234831
14	INC000004207331	Christine	Kim	Christine Kim	2/23/2023 11:31:17 AM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Sudiksha Niroula	Closed	3/1/2023 8:38:42 AM		Christopher G Cabotaje	INC000004193378
15	INC000004216124	Victor	Ferreira	Victor Ferreira	3/1/2023 8:57:41 AM	Mobile Device: Request to remove old iPhone 6 from Intune	User Service Restoration	MDM (Mobile Device Management) Tier 2	Alexander Koutroubas	Closed	3/1/2023 8:59:14 AM		Christopher J Ledbetter	INC000004222798
16	INC000004205590	Leah	Haskett	Leah Haskett	2/22/2023 2:44:58 PM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Sudiksha Niroula	Closed	3/1/2023 9:04:25 AM		Christopher J Ledbetter	INC000004243230
17	INC000004192038	Stella	Xa	Stella Xa	2/13/2023 1:49:22 PM	Tech Bar - Firefox DM access	User Service Restoration	IMACD	Walter R Monroy	Closed	3/1/2023 9:08:26 AM		Daulat D Miya	INC000004189683
18	INC000004214379	Joseph	Moreno	Joseph Moreno	2/28/2023 11:46:13 AM	Computer Hardware Issues - [Blue screen]	User Service Restoration	IMACD	Jason A Richardson	Closed	3/1/2023 9:26:13 AM		Daulat D Miya	INC000004221253
19	INC000004144918	Erica	Scott	Erica Scott	1/10/2023 2:13:29 PM	Laptop Request IMACD 20230116	User Service Request	Tech Bar	Robert Meza	Closed	3/1/2023 9:31:34 AM		Erich S Neuendorff	INC000004195094
20	INC000004215064	John	Berry	John Berry	2/28/2023 3:58:14 PM	Verizon MiFi/Jetpack - No data connection	User Service Restoration	MDM (Mobile Device Management) Tier 2	Alexander Koutroubas	Closed	3/1/2023 9:39:53 AM		Erich S Neuendorff	INC000004217777
21	INC000004197266	Ayah	Tamimi	Ayah Tamimi	2/16/2023 2:18:18 PM	Windows - not starting up	User Service Restoration	IMACD	Christopher G Cabotaje	Closed	3/1/2023 9:50:57 AM		Erick Gonzalez	INC000004204748
22	INC000004209571	Ignacio	Fernandez	Ignacio Fernandez	2/24/2023 10:04:55 AM	Computer Hardware Issues - Computer does not turn on	User Service Restoration	IMACD	Tommy Dinh	Closed	3/1/2023 9:57:37 AM		Erick Gonzalez	INC000004209319
23	INC000004123313	Harika	Sane	Harika Sane	12/21/2022 1:05:23 PM	Product Ordering for :Laptop	User Service Request	IMACD	Carolina Preciado	Closed	3/1/2023 10:00:59 AM		Ernesto Acompa	INC000004212621
24	INC000004204743	Maya	Beskar	Maya Beskar	2/22/2023 9:14:30 AM	Monitor - Display Settings issue	User Service Restoration	IMACD	Walter R Monroy	Closed	3/1/2023 10:03:59 AM		Ernesto Acompa	INC000004205373
25	INC000004214568	Christopher	Ingram	Christopher Ingram	2/28/2023 11:02:43 AM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Christopher G Cabotaje	Closed	3/1/2023 10:05:56 AM		Felipe F Frutos	INC000004238813
26	INC000004213157	Sara	Lenz	Sara Lenz	2/27/2023 3:31:03 PM	Windows - Slow Performance	User Service Restoration	Tech Bar	Daulat D Miya	Closed	3/1/2023 10:17:27 AM		Felipe F Frutos	INC000004232866
27	INC000004216112	Jennifer	Khwajazada	Jennifer Khwajazada	3/1/2023 8:49:32 AM	Computer Hardware Issues - [not turning on]	User Service Restoration	Tech Bar	Daulat D Miya	Closed	3/1/2023 10:19:41 AM		Fidel Deharo	INC000004212282
28	INC000004215052	Moses	Bravo	Moses Bravo	2/28/2023 3:31:35 PM	Hardware - Mouse / Keyboard Issue	User Service Restoration	IMACD	Bruce Bishara	Closed	3/1/2023 10:23:55 AM		Fidel Deharo	INC000004216319
29	INC000004179190	Jolyne	Chavolla	Jolyne Chavolla	2/3/2023 9:09:08 AM	Computer Hardware Issues - Screen is blinking	User Service Restoration	IMACD	Shanti Rokka	Closed	3/1/2023 10:26:05 AM		Gerard T Stanton	INC000004167669
30	INC000004216186	Enrique	Monagas	Enrique Monagas	3/1/2023 10:23:59 AM	Device does not boot up/Black Screen exists and could not log-in to device.	User Service Restoration	Tech Bar	Daulat D Miya	Closed	3/1/2023 10:32:26 AM		Gerard T Stanton	INC000004220304
DATA

Cell Formulas
Range	Formula
M3:M79	M3	=SORT(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,UNIQUE(I3:I1788),UNIQUE(FILTER(I3:I1788,COUNTIF(I3:I1788,I3:I1788)>1,"")))&"</c></p>","//c"))
N3:N30	N3	=IFERROR(INDEX(SORTBY(FILTER(A$3:A$1788,(I$3:I$1788=M3)*(A$3:A$1788<>N2)),RANDARRAY(COUNTIF(I$3:I$1788,M3)-(M2=M3))),1),"")
Dynamic array formulas.

 

[Fluff]

How many times do you want a name to appear, 3, 4 or 5times?

 

[Fluff]

If you want the names 3 times, try

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Incident ID*+	First Name+	Last Name+	FULL NAME	Submit Date	Summary*	Incident Type*	Assigned Group*+	Assignee+	Status*	Last Resolved Date		Technician	Audited Inncident
3	INC000004174281	Geoffery	Hewlett	Geoffery Hewlett	1/31/2023 7:47:23 PM	Laptop Request IMACD 20230221	User Service Request	IMACD Onboarding	Orlando A Roberts	Closed	03/01/2023 06:53		Alexander Koutroubas	INC000004215064
4	INC000004192652	Michael	Martin	Michael Martin	2/14/2023 6:37:14 AM	Laptop Request IMACD 20230221	User Service Request	IMACD Onboarding	Orlando A Roberts	Closed	03/01/2023 06:54		Alexander Koutroubas	INC000004216124
5	INC000004169215	James	Esquivel	James Esquivel	1/27/2023 12:15:26 PM	Product Ordering for :Monitor	User Service Request	IMACD	Christopher G Cabotaje	Closed	03/01/2023 07:41		Bruce Bishara	INC000004195410
6	INC000004206992	Justin	Surprenant	Justin Surprenant	2/23/2023 10:45:31 AM	CMS - Win7 - Re-image and rebuild CMS - Foreman role	User Service Restoration	FTA Support	Erick Gonzalez	Closed	03/01/2023 07:50		Bruce Bishara	INC000004215052
7	INC000004193290	Kumaraswamy	Purumala	Kumaraswamy Purumala	2/14/2023 12:22:43 PM	Product Ordering for :Laptop	User Service Request	IMACD Onboarding	Sudiksha Niroula	Closed	03/01/2023 07:53		Carolina Preciado	INC000004123313
8	INC000004193678	Romana	Samad	Romana Samad	2/14/2023 3:09:37 PM	Surface Laptop: Unable to connect to Hotspot	User Service Restoration	IMACD	Sudiksha Niroula	Closed	03/01/2023 08:03		Christopher G Cabotaje	INC000004193378
9	INC000004199235	Marian	Lafferty	Marian Lafferty	2/17/2023 11:14:29 AM	TechDay - Innovation Village 3	User Service Request	IMACD	Miguel Cisneros	Closed	03/01/2023 08:05		Christopher G Cabotaje	INC000004197266
10	INC000004179453	David	Flores	David Flores	02/03/2023 10:02	Report an issue : The battery is not working (this battery was a replacement)	User Service Restoration	IMACD	Sudiksha Niroula	Closed	03/01/2023 08:08		Christopher G Cabotaje	INC000004214568
11	INC000004195410	Todd	Gallaher	Todd Gallaher	2/15/2023 12:45:03 PM	Report an Issue - While using Microsoft Outlook I receive an error message (see attachment). Additio	User Service Restoration	IMACD	Bruce Bishara	Closed	03/01/2023 08:18		Daulat D Miya	INC000004216112
12	INC000004193378	Adam	Wires	Adam Wires	2/14/2023 11:31:07 AM	Device Return : Laptop;Monitor;PC Peripherals	User Service Request	IMACD - Off Boarding	Christopher G Cabotaje	Closed	03/01/2023 08:18		Daulat D Miya	INC000004213157
13	INC000004194891	Angelica	Pichay	Angelica Pichay	2/15/2023 10:41:56 AM	Hardware - Monitor Issue	User Service Restoration	IMACD	Sudiksha Niroula	Closed	03/01/2023 08:32		Daulat D Miya	INC000004216186
14	INC000004207331	Christine	Kim	Christine Kim	2/23/2023 11:31:17 AM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Sudiksha Niroula	Closed	03/01/2023 08:38		Erick Gonzalez	INC000004206992
15	INC000004216124	Victor	Ferreira	Victor Ferreira	03/01/2023 08:57	Mobile Device: Request to remove old iPhone 6 from Intune	User Service Restoration	MDM (Mobile Device Management) Tier 2	Alexander Koutroubas	Closed	03/01/2023 08:59		Jason A Richardson	INC000004214379
16	INC000004205590	Leah	Haskett	Leah Haskett	2/22/2023 2:44:58 PM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Sudiksha Niroula	Closed	03/01/2023 09:04		Miguel Cisneros	INC000004199235
17	INC000004192038	Stella	Xa	Stella Xa	2/13/2023 1:49:22 PM	Tech Bar - Firefox DM access	User Service Restoration	IMACD	Walter R Monroy	Closed	03/01/2023 09:08		Orlando A Roberts	INC000004174281
18	INC000004214379	Joseph	Moreno	Joseph Moreno	2/28/2023 11:46:13 AM	Computer Hardware Issues - [Blue screen]	User Service Restoration	IMACD	Jason A Richardson	Closed	03/01/2023 09:26		Orlando A Roberts	INC000004192652
19	INC000004144918	Erica	Scott	Erica Scott	01/10/2023 14:13	Laptop Request IMACD 20230116	User Service Request	Tech Bar	Robert Meza	Closed	03/01/2023 09:31		Robert Meza	INC000004144918
20	INC000004215064	John	Berry	John Berry	2/28/2023 3:58:14 PM	Verizon MiFi/Jetpack - No data connection	User Service Restoration	MDM (Mobile Device Management) Tier 2	Alexander Koutroubas	Closed	03/01/2023 09:39		Shanti Rokka	INC000004179190
21	INC000004197266	Ayah	Tamimi	Ayah Tamimi	2/16/2023 2:18:18 PM	Windows - not starting up	User Service Restoration	IMACD	Christopher G Cabotaje	Closed	03/01/2023 09:50		Sudiksha Niroula	INC000004179453
22	INC000004209571	Ignacio	Fernandez	Ignacio Fernandez	2/24/2023 10:04:55 AM	Computer Hardware Issues - Computer does not turn on	User Service Restoration	IMACD	Tommy Dinh	Closed	03/01/2023 09:57		Sudiksha Niroula	INC000004205590
23	INC000004123313	Harika	Sane	Harika Sane	12/21/2022 1:05:23 PM	Product Ordering for :Laptop	User Service Request	IMACD	Carolina Preciado	Closed	03/01/2023 10:00		Sudiksha Niroula	INC000004193678
24	INC000004204743	Maya	Beskar	Maya Beskar	2/22/2023 9:14:30 AM	Monitor - Display Settings issue	User Service Restoration	IMACD	Walter R Monroy	Closed	03/01/2023 10:03		Tommy Dinh	INC000004209571
25	INC000004214568	Christopher	Ingram	Christopher Ingram	2/28/2023 11:02:43 AM	Operational Request : Ad Hoc Hardware Request	User Service Request	IMACD	Christopher G Cabotaje	Closed	03/01/2023 10:05		Walter R Monroy	INC000004192038
26	INC000004213157	Sara	Lenz	Sara Lenz	2/27/2023 3:31:03 PM	Windows - Slow Performance	User Service Restoration	Tech Bar	Daulat D Miya	Closed	03/01/2023 10:17		Walter R Monroy	INC000004204743
27	INC000004216112	Jennifer	Khwajazada	Jennifer Khwajazada	03/01/2023 08:49	Computer Hardware Issues - [not turning on]	User Service Restoration	Tech Bar	Daulat D Miya	Closed	03/01/2023 10:19
28	INC000004215052	Moses	Bravo	Moses Bravo	2/28/2023 3:31:35 PM	Hardware - Mouse / Keyboard Issue	User Service Restoration	IMACD	Bruce Bishara	Closed	03/01/2023 10:23
29	INC000004179190	Jolyne	Chavolla	Jolyne Chavolla	02/03/2023 09:09	Computer Hardware Issues - Screen is blinking	User Service Restoration	IMACD	Shanti Rokka	Closed	03/01/2023 10:26
30	INC000004216186	Enrique	Monagas	Enrique Monagas	03/01/2023 10:23	Device does not boot up/Black Screen exists and could not log-in to device.	User Service Restoration	Tech Bar	Daulat D Miya	Closed	03/01/2023 10:32
31
Main

Cell Formulas
Range	Formula
M3:N26	M3	=LET(u,SORT(UNIQUE(FILTER(I3:I2000,I3:I2000<>""))),q,3,HSTACK(TOCOL(IF(SEQUENCE(,q)<=BYROW(COUNTIFS(I:I,u),LAMBDA(br,MIN(br,q))),u,1/0),2),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TAKE(SORTBY(FILTER(A3:A2000,I3:I2000=y),RANDARRAY(COUNTIFS(I:I,y))),q)))),1)))
Dynamic array formulas.

 

[robbarba]

Thank you for you're response I was out with COVID and I finally got back up! I have inserted the formula provided in M3 and I got back the following error #NAME?

 

[Fluff]

That means your Excel is not up-to-date. Do you have the BYROW function?

 

[robbarba]

grrr no I its not listed in the fx dropdown menu

 

[robbarba]

We are currently on v2202 (Build 14931.20944)

 

[Fluff]

You are one upgrade short, you need 2203 for those functions, I suspect that your IT have not released that upgrade yet.

 

[robbarba]

correct it works on my personal laptop, I can use "sneaker-net" untill we do !

 

[Fluff]

Ok , glad it's sorted & thanks for the feedback.