Combination
Random sample of journal lines with TAKE, SORTBY and RANDARRAY
Select n journal lines at random, never the same line twice: RANDARRAY gives every line a random key, SORTBY puts the lines in that order and TAKE keeps the first n.
Functions used:TAKESORTBYRANDARRAYROWSExcel 2024 or later (This combination needs Excel 2024 or later)
Worked example
Select 4 journal lines for detailed testing from a population of 12
Scenario
For the journal entry testing of Veldhuis Installatie BV you have 12 manual journal lines of 2025 from the XAF auditfile. You decide to vouch n = 4 lines at random to the supporting documents and the approval. Nobody should be able to call the selection selective, and the same line must not be picked twice.
The list here has 12 lines so it fits the grid; in practice the population is thousands of lines and the same formula works unchanged. The result is random: the lines shown are one illustrative draw, your sheet will show other lines.
The data
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Line no. | Date | Description | Amount | Sample size (n) | 44 | ||
| 2 | 25-5101 | 17 Jan 2025 | Revenue installation | €14,250.00€ 14.250,00 | Selected lines | 25-5111=TAKE(SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13))), G1)=TAKE(SORTBY(A2:A13; RANDARRAY(ROWS(A2:A13))); G1) | ||
| 3 | 25-5102 | 28 Feb 2025 | Accrued expenses | -€6,800.00€ -6.800,00 | 25-5103 | |||
| 4 | 25-5103 | 31 Mar 2025 | Depreciation | €3,920.00€ 3.920,00 | 25-5108 | |||
| 5 | 25-5104 | 9 May 2025 | Provision for bad debts | €5,400.00€ 5.400,00 | 25-5105 | |||
| 6 | 25-5105 | 30 Jun 2025 | Prepaid insurance | -€2,150.00€ -2.150,00 | ||||
| 7 | 25-5106 | 18 Jul 2025 | Reclassification of rent | €8,300.00€ 8.300,00 | ||||
| 8 | 25-5107 | 29 Aug 2025 | Bonus accrual | €12,600.00€ 12.600,00 | ||||
| 9 | 25-5108 | 30 Sep 2025 | Inventory adjustment | -€4,750.00€ -4.750,00 | ||||
| 10 | 25-5109 | 15 Oct 2025 | Correction of VAT | €1,875.00€ 1.875,00 | ||||
| 11 | 25-5110 | 28 Nov 2025 | Revaluation of equipment | €9,100.00€ 9.100,00 | ||||
| 12 | 25-5111 | 23 Dec 2025 | Release of provision | -€7,350.00€ -7.350,00 | ||||
| 13 | 25-5112 | 31 Dec 2025 | Year-end accrual | €15,480.00€ 15.480,00 | ||||
| 14 |
Swipe sideways to see all columns
Formula in G2
=TAKE(SORTBY(A2:A13,RANDARRAY(ROWS(A2:A13))), G1)
=TAKE(SORTBY(A2:A13;RANDARRAY(ROWS(A2:A13))); G1)
Result: 25-5111 / 25-5103 / 25-5108 / 25-510525-5111 / 25-5103 / 25-5108 / 25-5105Random: your result will differ
How the pieces fit together
Watch out: The formula is volatile: every edit anywhere in the workbook, and every time you open the file, draws a new sample. Never file the live formula as evidence.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=ROWS(A2:A13)
=ROWS(A2:A13)
ROWS RIJENThe number of rows in a range.Press Escape to closearrayThe range or array whose rows are counted.Line no. column, 12 cellsPress Escape to closeCounts the journal lines in the population:
A2:A13has 12 rows, so the population is 12. Because it counts the range, the next formulas follow when you paste a longer list and stretch the range.=RANDARRAY(ROWS(A2:A13))
=RANDARRAY(ROWS(A2:A13))
RANDARRAY ASELECT.MATRIXA spilled block of random numbers.Read more about RANDARRAYPress Escape to closeROWS RIJENThe number of rows in a range.Used here as: rowsHow many rows of random numbers; default 1.Press Escape to closearrayThe range or array whose rows are counted.Line no. column, 12 cellsPress Escape to closeOne random key per journal line: 12 decimal numbers between 0 and 1. They spill as a column of 12 and change on every recalculation.
This is random, so your numbers differ. Say the draw is 0.4127, 0.8530, 0.0961, 0.6742, 0.2318, 0.9075, 0.5586, 0.1803, 0.7394, 0.3269, 0.0447 and 0.6015, from line
25-5101down to25-5112.=SORTBY(A2:A13,
RANDARRAY(ROWS(A2:A13))) =SORTBY(A2:A13;
RANDARRAY(ROWS(A2:A13))) SORTBY SORTEREN.OPSorts a range by values in another range.Read more about SORTBYPress Escape to closearrayWhat you want back, sorted; the keys do not have to be part of it.Line no. column, 12 cellsPress Escape to closeRANDARRAY ASELECT.MATRIXA spilled block of random numbers.Used here as: by_array1The values that decide the order, one per row of array.Read more about RANDARRAYPress Escape to closeROWS RIJENThe number of rows in a range.Used here as: rowsHow many rows of random numbers; default 1.Press Escape to closearrayThe range or array whose rows are counted.Line no. column, 12 cellsPress Escape to closeAll 12 lines in random order. SORTBY sorts the line numbers ascending by the keys above. Sorting only reorders, so every line appears exactly once.
The smallest key is 0.0447 (
25-5111), then 0.0961 (25-5103), 0.1803 (25-5108), 0.2318 (25-5105), 0.3269 (25-5110), 0.4127 (25-5101), 0.5586 (25-5107), 0.6015 (25-5112), 0.6742 (25-5104), 0.7394 (25-5109), 0.8530 (25-5102) and 0.9075 (25-5106).=TAKE(SORTBY(A2:A13,
RANDARRAY(ROWS(A2:A13))), G1) =TAKE(SORTBY(A2:A13;
RANDARRAY(ROWS(A2:A13))); G1) SORTBY SORTEREN.OPSorts a range by values in another range.Used here as: arrayThe range or array to take rows or columns from.Read more about SORTBYPress Escape to closearrayWhat you want back, sorted; the keys do not have to be part of it.Line no. column, 12 cellsPress Escape to closeRANDARRAY ASELECT.MATRIXA spilled block of random numbers.Used here as: by_array1The values that decide the order, one per row of array.Read more about RANDARRAYPress Escape to closeROWS RIJENThe number of rows in a range.Used here as: rowsHow many rows of random numbers; default 1.Press Escape to closearrayThe range or array whose rows are counted.Line no. column, 12 cellsPress Escape to closerowsHow many rows to keep; negative counts from the end.Input cell: 4Press Escape to closeTAKE keeps the first 4 of the shuffled lines, the sample size in
G1. With the draw above that is25-5111,25-5103,25-5108and25-5105. They spill fromG2down toG5.Another draw gives another sample, but always 4 different lines.
Step by step
- ROWS(A2:A13) counts the population: 12 journal lines, from
25-5101to25-5112. - RANDARRAY(12) draws 12 random decimals between 0 and 1, one per line. This is the random part, so yours will differ. Say the draw is 0.4127 for
25-5101, 0.8530 for25-5102, 0.0961 for25-5103, 0.6742 for25-5104, 0.2318 for25-5105, 0.9075 for25-5106, 0.5586 for25-5107, 0.1803 for25-5108, 0.7394 for25-5109, 0.3269 for25-5110, 0.0447 for25-5111and 0.6015 for25-5112. - SORTBY sorts the 12 line numbers ascending by those keys: 0.0447, 0.0961, 0.1803, 0.2318, 0.3269, 0.4127, 0.5586, 0.6015, 0.6742, 0.7394, 0.8530, 0.9075. The shuffled order is
25-5111,25-5103,25-5108,25-5105,25-5110,25-5101,25-5107,25-5112,25-5104,25-5109,25-5102,25-5106. - TAKE(..., G1) keeps the first 4 of those 12, because
G1holds n = 4:25-5111,25-5103,25-5108and25-5105. They spill fromG2down toG5. - Whatever the draw, the result is 4 different lines out of the 12: sorting only reorders, so no line can appear twice. Only the choice of lines depends on the draw.
- Freeze the selection: copy
G2:G5and paste as values, and write the date and the population size (12) next to it. Without this step the lines change on the next recalculation and the file no longer shows what you tested.
Why it works
Giving every line an independent random key and sorting by it makes every ordering equally likely, so the first n lines are a fair random sample without replacement: every line has the same chance and none appears twice. ROWS ties the number of keys to the list, so the key array always has the size SORTBY demands.
RANDARRAY, SORTBY and TAKE need Excel for Microsoft 365 or Excel 2024 or later.
Watch out for
Instead of the live formula, file frozen values:
- copy the selected lines
- paste them as values
- note the date and the population size (here 12 lines) next to them
Then a reviewer can see what was selected and from how many.
- The draw cannot be repeated. If the file must show that the selection was fair, keep the frozen values together with the population, or choose a method with a documented start such as the systematic sample. A random draw you cannot repeat is only defensible when the pasted values and the population are in the file.
- If n is larger than the population, the sample is expected to equal the whole population without an error (Microsoft's TAKE page does not describe this case; test it in your own Excel). Check that
G1is smaller than ROWS(A2:A13); with n equal to the population the sample is the population as well.
Variations
=INDEX(A2:A13,RANDBETWEEN(1, ROWS(A2:A13)))
=INDEX(A2:A13;RANDBETWEEN(1; ROWS(A2:A13)))
One random line: RANDBETWEEN picks a position from 1 to 12 and INDEX returns the line at that position. Say the draw is 7: the result is 25-5107. Your draw will differ.
RANDBETWEEN alone can draw the same line twice: if you copy this formula into four cells, each cell draws independently and two of them may show the same line. For a sample of several different lines use the main formula.
=TAKE(SORTBY(A2:D13,RANDARRAY(ROWS(A2:D13))), G1)
=TAKE(SORTBY(A2:D13;RANDARRAY(ROWS(A2:D13))); G1)
The whole lines instead of only the line numbers: line number, date, description and amount of the 4 selected lines, so you can start vouching straight from the result. The shuffle is applied to the rows as a whole, so every line keeps its own date and amount.
Related combinations
Functions used
- TAKEKeeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.
- SORTBYSorts a range by the values of one or more other ranges or arrays, each with its own direction, and spills the sorted copy.
- RANDARRAYFills a block of any size with random numbers, decimals or whole numbers between bounds you choose, and draws them again on every recalculation.
Functions used
- TAKEKeeps only the first or last rows and columns of a range, spilled as a block; a negative count takes from the end.
- SORTBYSorts a range by the values of one or more other ranges or arrays, each with its own direction, and spills the sorted copy.
- RANDARRAYFills a block of any size with random numbers, decimals or whole numbers between bounds you choose, and draws them again on every recalculation.