TOROW

At Veldhuis Installatie BV your team drew a sample of purchase invoices. Sanne, Tom and Aisha each pasted their selection under their own name, so the sample sits in a grid of three columns. For the workpaper header you want all six invoice numbers on a single row.

Formula in E2

=TOROW(A2:C3)

=TOROW(A2:C3)

Result: 25-1101 | 25-1104 | 25-1109 | 25-1102 | 25-1105 | 25-111025-1101 | 25-1104 | 25-1109 | 25-1102 | 25-1105 | 25-1110

Watch out: If anything is typed in F2:J2, the formula shows #SPILL! instead of the row. Keep the cells to the right of a TOROW empty.

=TOROW(A2:C3, 0, TRUE)

=TOROW(A2:C3; 0; TRUE)

The client's bank reconciliation lists reconciling items in a grid: one column per bank account, with gaps where an account has no item on that line. You want one row with only the amounts, so you can count the items and compare their sum with the difference to explain.

A plain flattening would add a 0 for every gap.

Formula in E2

=TOROW(A2:C5, 1)

=TOROW(A2:C5; 1)

Result: €1,250.00 | €480.00 | €3,000.00 | -€725.00 | €1,900.00 | €615.00€ 1.250,00 | € 480,00 | € 3.000,00 | € -725,00 | € 1.900,00 | € 615,00

Watch out: A cell that looks empty but holds a space or a formula returning an empty text is not blank for ignore 1. It stays in the row as text. If the count is higher than the number of visible items, check those cells with ISBLANK.

=TOROW(A2:C5, 1, TRUE)

=TOROW(A2:C5; 1; TRUE)

From the receivables list of Veldhuis Installatie BV you select every invoice of at least the confirmation threshold in H1, to put in one cell of the confirmation request log. Invoice numbers per quarter sit in B:C and the amounts in D:E.

You want them listed quarter by quarter, so the Q3 invoices come before the Q4 invoices. Invoices below the threshold must not appear.

Formula in G3

=TEXTJOIN(", ", TRUE, TOROW(IF(D2:E6>=H1, B2:C6, NA()), 2, TRUE))

=TEXTJOIN(", "; TRUE; TOROW(IF(D2:E6>=H1; B2:C6; NA()); 2; TRUE))

Result: 25-2014, 25-2019, 25-2087, 25-2101, 25-2105

Watch out: Silent error: if a large amount has no invoice number (blank in B or C), IF returns 0 for that position and 0 appears in the text. Compare the number of items with a COUNTIF on the amounts.

=TEXTJOIN(", ", TRUE, TOROW(IF(D2:E6>=H1, B2:C6, NA()), 2))

=TEXTJOIN(", "; TRUE; TOROW(IF(D2:E6>=H1; B2:C6; NA()); 2))

=TEXTJOIN(", ", TRUE, IF(D2:E6>=H1, B2:C6, ""))

=TEXTJOIN(", "; TRUE; IF(D2:E6>=H1; B2:C6; ""))

For the duplicate payment test at Veldhuis Installatie BV you received three weekly payment files, pasted next to each other in columns A to C. Files differ in length, so the shorter ones end in blanks.

You want the invoice numbers that occur more than once anywhere in the grid, as a row for your workpaper, with a message when there are none.

Formula in E2

=LET(
  paid, TOROW(A2:C7, 1),
  distinct, UNIQUE(paid, TRUE),
  once, UNIQUE(paid, TRUE, TRUE),
  FILTER(distinct, ISNA(XMATCH(distinct, once)), "No duplicate payments")
)

=LET(
  paid; TOROW(A2:C7; 1);
  distinct; UNIQUE(paid; TRUE);
  once; UNIQUE(paid; TRUE; TRUE);
  FILTER(distinct; ISNA(XMATCH(distinct; once)); "No duplicate payments")
)

Result: 25-3105 | 25-3107 | 25-311025-3105 | 25-3107 | 25-3110

Watch out: The mirror-image mistake is leaving ignore at 0. The 4 blanks then come back as four 0 values, UNIQUE counts 0 four times, and the result gets a fourth hit, 0, next to the three real duplicates. It looks like a plausible extra finding.

=LET(
  paid, TOROW(A2:C7, 1),
  SORT(UNIQUE(FILTER(paid, ISNA(XMATCH(paid, UNIQUE(paid, TRUE, TRUE)))), TRUE), 1, 1, TRUE)
)

=LET(
  paid; TOROW(A2:C7; 1);
  SORT(UNIQUE(FILTER(paid; ISNA(XMATCH(paid; UNIQUE(paid; TRUE; TRUE)))); TRUE); 1; 1; TRUE)
)