Upgrade assist to a formula

R

robbarba

Board Regular
Joined
Apr 17, 2016
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
2Incident ID*+First Name+Last Name+FULL NAMESubmit DateSummary*Incident Type*Assigned Group*+Assignee+Status*Last Resolved DateTechnicianAudited Inncident
3INC000004174281GeofferyHewlettGeoffery Hewlett1/31/2023 7:47:23 PMLaptop Request IMACD 20230221User Service RequestIMACD OnboardingOrlando A RobertsClosed3/1/2023 6:53:27 AMAlexander KoutroubasINC000004230606
4INC000004192652MichaelMartinMichael Martin2/14/2023 6:37:14 AMLaptop Request IMACD 20230221User Service RequestIMACD OnboardingOrlando A RobertsClosed3/1/2023 6:54:31 AMAlexander KoutroubasINC000004235776
5INC000004169215JamesEsquivelJames Esquivel1/27/2023 12:15:26 PMProduct Ordering for :MonitorUser Service RequestIMACDChristopher G CabotajeClosed3/1/2023 7:41:40 AMAlfonso PhillipsINC000004243216
6INC000004206992JustinSurprenantJustin Surprenant2/23/2023 10:45:31 AMCMS - Win7 - Re-image and rebuild CMS - Foreman roleUser Service RestorationFTA SupportErick GonzalezClosed3/1/2023 7:50:57 AMAlfonso PhillipsINC000004245200
7INC000004193290KumaraswamyPurumalaKumaraswamy Purumala2/14/2023 12:22:43 PMProduct Ordering for :LaptopUser Service RequestIMACD OnboardingSudiksha NiroulaClosed3/1/2023 7:53:26 AMArmando GonzalesINC000004230886
8INC000004193678RomanaSamadRomana Samad2/14/2023 3:09:37 PMSurface Laptop: Unable to connect to HotspotUser Service RestorationIMACDSudiksha NiroulaClosed3/1/2023 8:03:37 AMArmando GonzalesINC000004242900
9INC000004199235MarianLaffertyMarian Lafferty2/17/2023 11:14:29 AMTechDay - Innovation Village 3User Service RequestIMACDMiguel CisnerosClosed3/1/2023 8:05:14 AMBruce BisharaINC000004197611
10INC000004179453DavidFloresDavid Flores2/3/2023 10:02:22 AMReport an issue : The battery is not working (this battery was a replacement)User Service RestorationIMACDSudiksha NiroulaClosed3/1/2023 8:08:37 AMBruce BisharaINC000004203057
11INC000004195410ToddGallaherTodd Gallaher2/15/2023 12:45:03 PMReport an Issue - While using Microsoft Outlook I receive an error message (see attachment). AdditioUser Service RestorationIMACDBruce BisharaClosed3/1/2023 8:18:30 AMCarolina PreciadoINC000004123313
12INC000004193378AdamWiresAdam Wires2/14/2023 11:31:07 AMDevice Return : Laptop;Monitor;PC PeripheralsUser Service RequestIMACD - Off BoardingChristopher G CabotajeClosed3/1/2023 8:18:44 AMCarolina PreciadoINC000004149497
13INC000004194891AngelicaPichayAngelica Pichay2/15/2023 10:41:56 AMHardware - Monitor IssueUser Service RestorationIMACDSudiksha NiroulaClosed3/1/2023 8:32:58 AMChristopher G CabotajeINC000004234831
14INC000004207331ChristineKimChristine Kim2/23/2023 11:31:17 AMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDSudiksha NiroulaClosed3/1/2023 8:38:42 AMChristopher G CabotajeINC000004193378
15INC000004216124VictorFerreiraVictor Ferreira3/1/2023 8:57:41 AMMobile Device: Request to remove old iPhone 6 from IntuneUser Service RestorationMDM (Mobile Device Management) Tier 2Alexander KoutroubasClosed3/1/2023 8:59:14 AMChristopher J LedbetterINC000004222798
16INC000004205590LeahHaskettLeah Haskett2/22/2023 2:44:58 PMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDSudiksha NiroulaClosed3/1/2023 9:04:25 AMChristopher J LedbetterINC000004243230
17INC000004192038StellaXaStella Xa2/13/2023 1:49:22 PMTech Bar - Firefox DM accessUser Service RestorationIMACDWalter R MonroyClosed3/1/2023 9:08:26 AMDaulat D MiyaINC000004189683
18INC000004214379JosephMorenoJoseph Moreno2/28/2023 11:46:13 AMComputer Hardware Issues - [Blue screen]User Service RestorationIMACDJason A RichardsonClosed3/1/2023 9:26:13 AMDaulat D MiyaINC000004221253
19INC000004144918EricaScottErica Scott1/10/2023 2:13:29 PMLaptop Request IMACD 20230116User Service RequestTech BarRobert MezaClosed3/1/2023 9:31:34 AMErich S NeuendorffINC000004195094
20INC000004215064JohnBerryJohn Berry2/28/2023 3:58:14 PMVerizon MiFi/Jetpack - No data connectionUser Service RestorationMDM (Mobile Device Management) Tier 2Alexander KoutroubasClosed3/1/2023 9:39:53 AMErich S NeuendorffINC000004217777
21INC000004197266AyahTamimiAyah Tamimi2/16/2023 2:18:18 PMWindows - not starting upUser Service RestorationIMACDChristopher G CabotajeClosed3/1/2023 9:50:57 AMErick GonzalezINC000004204748
22INC000004209571IgnacioFernandezIgnacio Fernandez2/24/2023 10:04:55 AMComputer Hardware Issues - Computer does not turn onUser Service RestorationIMACDTommy DinhClosed3/1/2023 9:57:37 AMErick GonzalezINC000004209319
23INC000004123313HarikaSaneHarika Sane12/21/2022 1:05:23 PMProduct Ordering for :LaptopUser Service RequestIMACDCarolina PreciadoClosed3/1/2023 10:00:59 AMErnesto AcompaINC000004212621
24INC000004204743MayaBeskarMaya Beskar2/22/2023 9:14:30 AMMonitor - Display Settings issueUser Service RestorationIMACDWalter R MonroyClosed3/1/2023 10:03:59 AMErnesto AcompaINC000004205373
25INC000004214568ChristopherIngramChristopher Ingram2/28/2023 11:02:43 AMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDChristopher G CabotajeClosed3/1/2023 10:05:56 AMFelipe F FrutosINC000004238813
26INC000004213157SaraLenzSara Lenz2/27/2023 3:31:03 PMWindows - Slow PerformanceUser Service RestorationTech BarDaulat D MiyaClosed3/1/2023 10:17:27 AMFelipe F FrutosINC000004232866
27INC000004216112JenniferKhwajazadaJennifer Khwajazada3/1/2023 8:49:32 AMComputer Hardware Issues - [not turning on]User Service RestorationTech BarDaulat D MiyaClosed3/1/2023 10:19:41 AMFidel DeharoINC000004212282
28INC000004215052MosesBravoMoses Bravo2/28/2023 3:31:35 PMHardware - Mouse / Keyboard IssueUser Service RestorationIMACDBruce BisharaClosed3/1/2023 10:23:55 AMFidel DeharoINC000004216319
29INC000004179190JolyneChavollaJolyne Chavolla2/3/2023 9:09:08 AMComputer Hardware Issues - Screen is blinkingUser Service RestorationIMACDShanti RokkaClosed3/1/2023 10:26:05 AMGerard T StantonINC000004167669
30INC000004216186EnriqueMonagasEnrique Monagas3/1/2023 10:23:59 AMDevice does not boot up/Black Screen exists and could not log-in to device.User Service RestorationTech BarDaulat D MiyaClosed3/1/2023 10:32:26 AMGerard T StantonINC000004220304
DATA
Cell Formulas
RangeFormula
M3:M79M3=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:N30N3=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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
How many times do you want a name to appear, 3, 4 or 5times?
 
Upvote 0
If you want the names 3 times, try
Fluff.xlsm
ABCDEFGHIJKLMN
1
2Incident ID*+First Name+Last Name+FULL NAMESubmit DateSummary*Incident Type*Assigned Group*+Assignee+Status*Last Resolved DateTechnicianAudited Inncident
3INC000004174281GeofferyHewlettGeoffery Hewlett1/31/2023 7:47:23 PMLaptop Request IMACD 20230221User Service RequestIMACD OnboardingOrlando A RobertsClosed03/01/2023 06:53Alexander KoutroubasINC000004215064
4INC000004192652MichaelMartinMichael Martin2/14/2023 6:37:14 AMLaptop Request IMACD 20230221User Service RequestIMACD OnboardingOrlando A RobertsClosed03/01/2023 06:54Alexander KoutroubasINC000004216124
5INC000004169215JamesEsquivelJames Esquivel1/27/2023 12:15:26 PMProduct Ordering for :MonitorUser Service RequestIMACDChristopher G CabotajeClosed03/01/2023 07:41Bruce BisharaINC000004195410
6INC000004206992JustinSurprenantJustin Surprenant2/23/2023 10:45:31 AMCMS - Win7 - Re-image and rebuild CMS - Foreman roleUser Service RestorationFTA SupportErick GonzalezClosed03/01/2023 07:50Bruce BisharaINC000004215052
7INC000004193290KumaraswamyPurumalaKumaraswamy Purumala2/14/2023 12:22:43 PMProduct Ordering for :LaptopUser Service RequestIMACD OnboardingSudiksha NiroulaClosed03/01/2023 07:53Carolina PreciadoINC000004123313
8INC000004193678RomanaSamadRomana Samad2/14/2023 3:09:37 PMSurface Laptop: Unable to connect to HotspotUser Service RestorationIMACDSudiksha NiroulaClosed03/01/2023 08:03Christopher G CabotajeINC000004193378
9INC000004199235MarianLaffertyMarian Lafferty2/17/2023 11:14:29 AMTechDay - Innovation Village 3User Service RequestIMACDMiguel CisnerosClosed03/01/2023 08:05Christopher G CabotajeINC000004197266
10INC000004179453DavidFloresDavid Flores02/03/2023 10:02Report an issue : The battery is not working (this battery was a replacement)User Service RestorationIMACDSudiksha NiroulaClosed03/01/2023 08:08Christopher G CabotajeINC000004214568
11INC000004195410ToddGallaherTodd Gallaher2/15/2023 12:45:03 PMReport an Issue - While using Microsoft Outlook I receive an error message (see attachment). AdditioUser Service RestorationIMACDBruce BisharaClosed03/01/2023 08:18Daulat D MiyaINC000004216112
12INC000004193378AdamWiresAdam Wires2/14/2023 11:31:07 AMDevice Return : Laptop;Monitor;PC PeripheralsUser Service RequestIMACD - Off BoardingChristopher G CabotajeClosed03/01/2023 08:18Daulat D MiyaINC000004213157
13INC000004194891AngelicaPichayAngelica Pichay2/15/2023 10:41:56 AMHardware - Monitor IssueUser Service RestorationIMACDSudiksha NiroulaClosed03/01/2023 08:32Daulat D MiyaINC000004216186
14INC000004207331ChristineKimChristine Kim2/23/2023 11:31:17 AMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDSudiksha NiroulaClosed03/01/2023 08:38Erick GonzalezINC000004206992
15INC000004216124VictorFerreiraVictor Ferreira03/01/2023 08:57Mobile Device: Request to remove old iPhone 6 from IntuneUser Service RestorationMDM (Mobile Device Management) Tier 2Alexander KoutroubasClosed03/01/2023 08:59Jason A RichardsonINC000004214379
16INC000004205590LeahHaskettLeah Haskett2/22/2023 2:44:58 PMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDSudiksha NiroulaClosed03/01/2023 09:04Miguel CisnerosINC000004199235
17INC000004192038StellaXaStella Xa2/13/2023 1:49:22 PMTech Bar - Firefox DM accessUser Service RestorationIMACDWalter R MonroyClosed03/01/2023 09:08Orlando A RobertsINC000004174281
18INC000004214379JosephMorenoJoseph Moreno2/28/2023 11:46:13 AMComputer Hardware Issues - [Blue screen]User Service RestorationIMACDJason A RichardsonClosed03/01/2023 09:26Orlando A RobertsINC000004192652
19INC000004144918EricaScottErica Scott01/10/2023 14:13Laptop Request IMACD 20230116User Service RequestTech BarRobert MezaClosed03/01/2023 09:31Robert MezaINC000004144918
20INC000004215064JohnBerryJohn Berry2/28/2023 3:58:14 PMVerizon MiFi/Jetpack - No data connectionUser Service RestorationMDM (Mobile Device Management) Tier 2Alexander KoutroubasClosed03/01/2023 09:39Shanti RokkaINC000004179190
21INC000004197266AyahTamimiAyah Tamimi2/16/2023 2:18:18 PMWindows - not starting upUser Service RestorationIMACDChristopher G CabotajeClosed03/01/2023 09:50Sudiksha NiroulaINC000004179453
22INC000004209571IgnacioFernandezIgnacio Fernandez2/24/2023 10:04:55 AMComputer Hardware Issues - Computer does not turn onUser Service RestorationIMACDTommy DinhClosed03/01/2023 09:57Sudiksha NiroulaINC000004205590
23INC000004123313HarikaSaneHarika Sane12/21/2022 1:05:23 PMProduct Ordering for :LaptopUser Service RequestIMACDCarolina PreciadoClosed03/01/2023 10:00Sudiksha NiroulaINC000004193678
24INC000004204743MayaBeskarMaya Beskar2/22/2023 9:14:30 AMMonitor - Display Settings issueUser Service RestorationIMACDWalter R MonroyClosed03/01/2023 10:03Tommy DinhINC000004209571
25INC000004214568ChristopherIngramChristopher Ingram2/28/2023 11:02:43 AMOperational Request : Ad Hoc Hardware RequestUser Service RequestIMACDChristopher G CabotajeClosed03/01/2023 10:05Walter R MonroyINC000004192038
26INC000004213157SaraLenzSara Lenz2/27/2023 3:31:03 PMWindows - Slow PerformanceUser Service RestorationTech BarDaulat D MiyaClosed03/01/2023 10:17Walter R MonroyINC000004204743
27INC000004216112JenniferKhwajazadaJennifer Khwajazada03/01/2023 08:49Computer Hardware Issues - [not turning on]User Service RestorationTech BarDaulat D MiyaClosed03/01/2023 10:19
28INC000004215052MosesBravoMoses Bravo2/28/2023 3:31:35 PMHardware - Mouse / Keyboard IssueUser Service RestorationIMACDBruce BisharaClosed03/01/2023 10:23
29INC000004179190JolyneChavollaJolyne Chavolla02/03/2023 09:09Computer Hardware Issues - Screen is blinkingUser Service RestorationIMACDShanti RokkaClosed03/01/2023 10:26
30INC000004216186EnriqueMonagasEnrique Monagas03/01/2023 10:23Device does not boot up/Black Screen exists and could not log-in to device.User Service RestorationTech BarDaulat D MiyaClosed03/01/2023 10:32
31
Main
Cell Formulas
RangeFormula
M3:N26M3=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.
 
Upvote 0
Solution
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?
 
Upvote 0
That means your Excel is not up-to-date. Do you have the BYROW function?
 
Upvote 0
You are one upgrade short, you need 2203 for those functions, I suspect that your IT have not released that upgrade yet.
 
Upvote 0
correct it works on my personal laptop, I can use "sneaker-net" untill we do !
 
Upvote 0
Ok (y), glad it's sorted & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
96
kumara_faith
K
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
283
Artik
Artik
H
  • Solved
Delete the last character of cells in column "K" if it is equal to "X" or "x"
Replies
8
Views
162
Fuji
F
M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top