TOCOL

You drew a sample of sales invoices at Veldhuis Installatie BV in two rounds. The invoice numbers sit in a small grid, three per round. For the working paper you want one list with every number under each other, in the order of the grid.

Formula in D2

=TOCOL(A2:B4)

=TOCOL(A2:B4)

Result: 25-1101 / 25-1108 / 25-1115 / 25-1122 / 25-1130 / 25-113725-1101 / 25-1108 / 25-1115 / 25-1122 / 25-1130 / 25-1137

Watch out: The numbers come out row by row, not per round. If you expected all of round 1 first, you need the third argument TRUE.

=TOCOL(A2:B4, 0, TRUE)

=TOCOL(A2:B4; 0; TRUE)

In the bank reconciliation of Veldhuis Installatie BV the client lists open items per week and per bank account. Most weeks an account has nothing open, so many cells are empty. You want one list of the amounts that are open, without zeros for the empty cells, to check them against the bank statement.

Formula in E2

=TOCOL(A2:C5, 1)

=TOCOL(A2:C5; 1)

Result: €1,250.00 / €480.00 / €2,100.00 / €760.00 / €315.00 / €4,400.00€ 1.250,00 / € 480,00 / € 2.100,00 / € 760,00 / € 315,00 / € 4.400,00

Watch out: A cell that contains an empty text from a formula ("") is not blank, so ignore 1 keeps it. If an empty-looking gap survives, test the cell with ISBLANK.

=TOCOL(A2:C5, 3)

=TOCOL(A2:C5; 3)

For a payment test at Veldhuis Installatie BV the client gave two matching grids: the invoice numbers and, in the same positions, the amounts. You want the first invoice whose amount is at or above performance materiality, which sits in G1, and not search for it by eye. The grids have two columns and five rows.

Formula in F3

=XLOOKUP(TRUE, TOCOL(C2:D6>=G1), TOCOL(A2:B6), "No invoice at or above materiality")

=XLOOKUP(TRUE; TOCOL(C2:D6>=G1); TOCOL(A2:B6); "No invoice at or above materiality")

Result: 25-2004

Watch out: If one TOCOL reads by column and the other by row, the lists no longer line up. With the amounts read by column and the invoices by row, position 5 is TRUE and the formula returns 25-2005, a real invoice number that is wrong, with no error.

=XLOOKUP(TRUE, TOCOL(C2:D6>=G1, 0, TRUE), TOCOL(A2:B6, 0, TRUE), "No invoice at or above materiality")

=XLOOKUP(TRUE; TOCOL(C2:D6>=G1; 0; TRUE); TOCOL(A2:B6; 0; TRUE); "No invoice at or above materiality")

=FILTER(TOCOL(A2:B6), TOCOL(C2:D6>=G1))

=FILTER(TOCOL(A2:B6); TOCOL(C2:D6>=G1))

The payment files of three bank runs at Veldhuis Installatie BV are pasted next to each other, one column per run, with different numbers of invoices so the lower cells are empty.

You test for duplicate payments: which invoice numbers appear more than once anywhere in the three runs? The reviewer wants the result ordered by run and the formula readable.

Formula in E2

=LET(
  paid, TOCOL(A2:C6, 1, TRUE),
  times, COUNTIF(A2:C6, paid),
  repeated, FILTER(paid, times > 1, "No invoice paid twice"),
  UNIQUE(repeated)
)

=LET(
  paid; TOCOL(A2:C6; 1; TRUE);
  times; COUNTIF(A2:C6; paid);
  repeated; FILTER(paid; times > 1; "No invoice paid twice");
  UNIQUE(repeated)
)

Result: F25-3102 / F25-3105 / F25-3107F25-3102 / F25-3105 / F25-3107

Watch out: The mirror-image mistake is ignore 3 'to be safe'. It also drops error cells, such as #N/A from a broken link in the export, so a payment that failed to load disappears from the test without a trace. Use 1 and look at errors first.

=LET(
  paid, TOCOL(A2:C6, 1),
  UNIQUE(FILTER(paid, COUNTIF(A2:C6, paid) > 1, "No invoice paid twice"))
)

=LET(
  paid; TOCOL(A2:C6; 1);
  UNIQUE(FILTER(paid; COUNTIF(A2:C6; paid) > 1; "No invoice paid twice"))
)