Shuffle Rows & Draw a Sample Without Replacement (SORTBY + TAKE)

Sampling rows without replacement

Independent INDEX plus RANDBETWEEN draws can repeat a row. With three draws from six equally likely rows, the probability of at least one repeat is 1−(6×5×4)/6^3 = 4/9, about 44.44%.

Sorting the entire list once and taking its first K rows selects each source row at most once. This does not remove duplicate people or duplicate values from the input. If one person has multiple source records, clean or consolidate those records before drawing one winner per person.

In this example A2:A6 contains names, B2:B6 contains fixed demonstration keys, C2:C6 contains unique numeric record IDs and D2 contains K. Different people may share a name; the IDs, not the displayed names, identify the sampled records.

Generate and freeze the keys

For a new draw, enter =RAND() in B2 and fill down through B6, then copy B2:B6 and paste values over the same cells before recording the result. Excel users can alternatively generate a column with =RANDARRAY(ROWS(A2:A6)); RANDARRAY is not currently implemented in Arte Sheets.

Keep the frozen keys, input roster, chosen K and result together. Fixed hand-entered keys make this tutorial reproducible but are not themselves evidence of a random draw. Pasting keys as values is not seeding a generator.

With independent identically distributed continuous random keys, each ordering is equally likely. Computer-generated keys have finite precision, so ties are possible. The audit here rejects ties: redraw the entire key vector and freeze it again. Do not resolve ties by favouring one entrant. Tests below verify ordering and row uniqueness, not the statistical quality of the random-number generator.

Audit the source rows

F2 contains this audit. All five names must be present, keys must be numeric in [0,1), and IDs must be numeric and unique. F3 validates K with =IF(NOT(ISNUMBER(D2)),"Invalid Sample Size K",IF(OR(D2<>INT(D2),D2<1,D2>ROWS(A2:A6)),"Invalid Sample Size K","OK")).

Extend every paired range and validation range together for a longer roster. Do not use COUNTA alone to infer a valid source size when there are holes in the range.

=IF(COUNTBLANK(A2:A6)>0,"Fill every entrant name",IF(COUNT(B2:C6)<>10,"Enter numeric keys and IDs",IF(OR(MIN(B2:B6)<0,MAX(B2:B6)>=1),"Keys must be in [0,1)",IF(OR(COUNTIF(C2:C6,C2)>1,COUNTIF(C2:C6,C3)>1,COUNTIF(C2:C6,C4)>1,COUNTIF(C2:C6,C5)>1,COUNTIF(C2:C6,C6)>1),"IDs must be unique",IF(OR(COUNTIF(B2:B6,B2)>1,COUNTIF(B2:B6,B3)>1,COUNTIF(B2:B6,B4)>1,COUNTIF(B2:B6,B5)>1,COUNTIF(B2:B6,B6)>1),"Tied keys: redraw and freeze all keys","OK")))))

Draw K rows from the frozen ordering

F4 displays the selected names. Initially K=3 returns Mateo, Chloe, Aisha; F5 displays their IDs 102, 104, 101. K must be an integer from 1 to 5.

After F2 and F3 both show OK, =TAKE(SORTBY(A2:C6,B2:B6,1),D2) creates a separate three-column result retaining names, keys and IDs together. Place it in an empty area, for example H2:J6, outside the source data. The example checks the full returned table, not just its first value.

TEXTJOIN produces a convenient display string, not a machine-readable export format: names containing commas remain intact in the three-column table.

=IF(F2<>"OK",F2,IF(F3<>"OK",F3,TEXTJOIN(", ",TRUE,TAKE(SORTBY(A2:A6,B2:B6,1),D2))))

Inspect the first record and the full ordering

F6 shows the first name after validating the source. The full initial order is Mateo, Chloe, Aisha, Lucas, Devon. To display every record, use =SORTBY(A2:C6,B2:B6,1) in a separate empty area after the audit passes.

Do not regenerate the random keys separately for each output column or each winner. All outputs must refer to the same frozen key column so records remain aligned.

=IF(F2<>"OK",F2,INDEX(SORTBY(A2:A6,B2:B6,1),1))

Further reading

• Microsoft Support — SORTBY function
• Microsoft Support — TAKE function