Dynamic arrays › TOCOL102 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeTOCOL

TOCOL: duty roster into one column

Ratna, head of administration at a community health center (puskesmas) in Garut, keeps the front desk roster as a day × shift table. The treasurer needs every staff name in one column to work out the per-shift transport allowance, and empty slots must be left out.

Task

In E2, write one TOCOL formula that turns the roster B2:C7 into a single column, read row by row (Monday morning, Monday afternoon, Tuesday morning and so on), ignoring blank cells. The result spills into E2:E10.

Cells to fillE2:E10

Required functionsTOCOL

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
TOCOL(array, [ignore], [scan_by_column]) flattens a table into one column. By default the table is read row by row.
Hint 2
The ignore argument: 0 = keep everything, 1 = skip blanks, 2 = skip errors, 3 = skip both.
Hint 3
Pattern: =TOCOL(B2:C7, …). Without the second argument, empty slots show up as 0.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

E2

=TOCOL(B2:C7,1)

Explanation

=TOCOL(B2:C7,1) reads the roster row by row and skips the empty slots, giving 9 names. From this list the treasurer can count each person's shifts with COUNTIF. To read column by column (all morning shifts first, then afternoons), set the third argument to TRUE. Its sibling, TOROW, flattens a table sideways.

Starting worksheet: Sheet1
Row	A	B	C	E
1	Day	Morning	Afternoon	Staff
2	Monday	Rina	Bayu
3	Tuesday	Dewi
4	Wednesday	Rina	Fajar
5	Thursday	Bayu
6	Friday	Dewi	Fajar
7	Saturday	Ayu
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result