Text list divided into columns

[ACMjocelyn]

I am trying to find a formula or VBA code

Here is my problem:
We are conducting an external audit. There are agencies withdifferent number of employee files this is not a fixed number. I input a formulathat gives me the sample size based on the total population(already done). Sowhat I am having trouble with is pasting that list of employees andhighlighting the sample size that my formula has calculated.
For example I paste all 250 names on column B but my samplesize is only 60 I need Column F to list only the 60 random names from the list.I have done this with the formula below but some cells will have a 0? And I canÂ’tseem to restricted it to only the sample size of 60 in this case or whatever itmay be for other agencies.
=INDEX($B:$B,RANDBETWEEN(1,COUNTA($B:$B)))

For the separate quarters (4) columns G-J I have came upwith this formula :

=INDEX($F$17:$F$200,ROW(G16)+(($D$15/4)*(COLUMNS($G$1:G$1)-1)))
$D$15= the calculatedsample size (not fixed)
But itÂ’s not dividing evenly and itÂ’s give me zeros (0).

Helpppp please!!

 

[Rijnsent]

Re: HELP! Text list divided into columns

Hi Jocelyn,
did you try this manual: https://www.ablebits.com/office-add...m-sample/#random-selection-without-duplicates ?
They basically add one column with a random number (=RAND()) from 0 to 1 and a column for the sample with the formula: =INDEX($A$2:$A$16, RANK(B2,$B$2:$B$16), 1)
Hope that helps,
Koen