Repeat formula through column X times

[ladylissa21]

Hello! I am using a randomizing formula to identify case numbers to audit - I paste a list of case numbers in column A, then the sheet tells me how many I need to audit, and provides a randomized list.

I would like the result to show me a list of only the number of case numbers that were identified. For example, If I need to review 5 cases out of 10, I want it to give me 5 randomized cases. I know I can just drag the index formula down as needed, but I will be working with thousands of cases and updating the list in column A very frequently. Is there a formula to randomly identify a percentage of the values in A, based on the number in D3?

Alternately, is there a formula I could enter next to a list of case numbers and have it give me a yes or no result on 50% of the selected cases? So, something to enter in column B and drag down, that would just identify 50% rather than having it refer to an alternate cell count?

 

[Fluff]

@ladylissa21 what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ladylissa21]

@ladylissa21 what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for the tip; I'll do that! I'm on version 2405 of Microsoft 365.

 

[Fluff]

Thanks for that.
How about

=LET(d,FILTER(A1:A10000,A1:A10000<>""),r,ROUNDUP(ROWS(d)*D2,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))

 

[ladylissa21]

Thanks for that.
How about

=LET(d,FILTER(A1:A10000,A1:A10000<>""),r,ROUNDUP(ROWS(d)*D2,0),TAKE(SORTBY(d,RANDARRAY(ROWS(d))),r))

That was exactly what I needed, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ladylissa21]

You're welcome & thanks for the feedback.

Sorry, one more question. Would it be possible to return a value of "Audit" in the random selection, rather than the case number?

 

[Fluff]

In what way?

 

[ladylissa21]

In what way?

Nevermind actually - what I was thinking doesn't make any sense. Thanks again!

 

[Fluff]

Ok