01 / 12Three Drawings Went to Site Out of Date

Sorting and FilteringText FunctionsDynamic Arrays

Sort Alphanumeric Codes in Excel So 10 Comes After 9

Steel for Grid A–D Was Cut to Revision 9 of a Drawing on Revision 11, Because the Register Sorted R10 and R11 Ahead of R2

29 Oct 20269 min read

UsesSORTTEXTBEFORE, TEXTAFTERVALUEMIN, MAX

Harbrook Construction's issue sheet told site which revision of each drawing to build from. In September it sent three drawings out of date. The register was complete and correct; the sort that read it put R10, R11 and R12 ahead of R2.

The sheet took the last row for each drawing in a register sorted A to Z. For any drawing with ten or more revisions, the last row was R9.

Stale issue, SeptemberFigure
Drawings in the register8
Revisions issued67
Drawings sent at the wrong revision3
Revisions site never saw6
Ground-beam rebar re-fixed (S-101)£8,450
Steelwork remade, grid A–D (S-103)£27,600

Scope. SORTBY and FILTER need Excel 2021 or later, and TEXTAFTER needs Excel 2024 or Microsoft 365. Each section gives an older-Excel version beside them, built from MID, VALUE and MAXIFS, which needs Excel 2019.

Throughout, the sheet called Register holds one row per issued revision in A2:B68: the drawing in column A and the revision code, such as R12, in column B.

01Three Drawings Went to Site Out of Date

The document controller logs every issue as a row: drawing, revision, date. The issue sheet looks up each drawing's current revision from that log and prints it on the transmittal that goes to site.

On 11 September the fabricator started cutting steel for grid A–D from S-103 revision 9. Revisions 10 and 11 had already been issued, and between them they changed the base plates and two column sections.

Eight Drawings, Sixty-Seven Revisions and Three Sent to Site at R9

Harbrook Construction's issue summary, in A1:E9: one row per drawing. Column B counts the revisions in the register, column C is what the issue sheet sent to site by taking the last row of a register sorted A to Z, column D is the highest revision by number, and column E how many revisions site was behind.

ABCDE
1
Drawing
Revisions issued
Sent to site (A→Z)
Current revision
Revisions behind
2
S-101 Ground beams
12
R9
R12
3
3
S-102 Pile caps
6
R6
R6
0
4
S-103 Steel frame A–D
11
R9
R11
2
5
S-104 Steel frame E–H
8
R8
R8
0
6
S-105 Roof steel
10
R9
R10
1
7
S-106 Stair cores
4
R4
R4
0
8
S-107 Connections
7
R7
R7
0
9
S-108 Retaining wall
9
R9
R9
0

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

Two other drawings were stale too. S-101 was caught at the pre-pour inspection, with the rebar cage fixed to R9. S-105 was caught before anyone fabricated from it.

02Text Sorts One Character at a Time

R12 is text, not a number, so Excel sorts it the way a dictionary would. It compares the first character, then the second, and stops at the first difference.

R10 and R2 share the R. Then 1 meets 2, and 1 comes first, so R10 sorts before R2 whatever follows. Type ="R10"<"R2" into any cell and it returns TRUE.

A to Z orderWhat Excel compared
R1R1 is shortest
R101 before 2
R111 before 2
R121 before 2
R2first 2
R9last, because 9 is the highest first digit

For a drawing with nine revisions or fewer the order is right. The tenth revision is where it breaks, which is why the register looked fine for most of the job.

Scenario: In an empty column, type R1 to R12 one per row, then sort it A to Z with Data ▸ Sort. Check which code lands in the last row, then enter ="R10"<"R2" beside it.

Try it in the grid

03The Last Row Was the Wrong Row

The issue sheet's formula was sound on its own terms. With the register sorted by drawing and then revision, it fetched the last match for each drawing.

=XLOOKUP(A2,Register!$A$2:$A$68,Register!$B$2:$B$68,,0,-1)

The -1 makes XLOOKUP search from the bottom up. It returns the last row, not the highest revision, and it trusts the sort to have put them in the same place.

DrawingRevisions issuedSent to siteCurrent
S-101 Ground beams12R9R12
S-103 Steel frame A–D11R9R11
S-105 Roof steel10R9R10
S-108 Retaining wall9R9R9

S-108 is the one to notice. It shows R9, and R9 is right. The same answer on four rows was right once and wrong three times.

Scenario: Sort a register of S-101's twelve revisions A to Z, then enter =XLOOKUP("S-101",A2:A13,B2:B13,,0,-1). Confirm it returns R9, then sort Z to A and watch it return R1.

Try it in the grid

04MAX Cannot Read R12

The obvious repair is to ask for the highest revision instead of the last one. =MAXIFS(Register!$B$2:$B$68,Register!$A$2:$A$68,A2) looks right and returns 0 for every drawing.

MAX and MAXIFS ignore text completely. They find no numbers in column B, so they return 0 rather than an error. A zero on a transmittal reads as "no revision yet", which is its own kind of wrong.

=MAX(Register!B2:B68) does the same. The fix is to give Excel the number it is being asked to compare, which the next section does.

05Pull the Number Into Its Own Column

Everything after the R is the revision number. Put it in a helper column as a real number, and every sort, lookup and maximum works again.

=--TEXTAFTER(B2,"R")

TEXTAFTER returns the text after the first R, so "12", and the two minus signs turn that into the number 12. In older Excel, =VALUE(MID(B2,2,9)) does the same: it takes up to nine characters from the second one on.

Fill it down column C of the register. Now C2:C68 holds 1 to 12, and a number column sorts in number order.

Scenario: Add =--TEXTAFTER(B2,"R") in C2 of the register and fill it to C68. Then sort by column A and then column C, and check that S-101's last row is now R12.

Try it in the grid

06Sort by the Number, Show the Code

With a helper column, Data ▸ Sort takes two levels: Drawing A to Z, then the number column smallest to largest. In Microsoft 365 you can skip the helper and sort in a formula.

=SORTBY(Register!A2:B68,Register!A2:A68,1,--TEXTAFTER(Register!B2:B68,"R"),1)

SORTBY sorts the two columns by keys you compute. The second key is the number, never the code, so R10 lands after R9. The code itself is still what the sorted list shows.

A sort that runs in a formula cannot go stale. Whoever adds R13 next month does not have to remember to re-sort the register.

Scenario: Enter the SORTBY formula in an empty sheet and find S-103 in its result. Check that its rows run R1 to R11, then add an R12 to the register and watch it appear at the bottom.

Try it in the grid

07Find the Current Revision Without Sorting

The issue sheet never needed a sort at all. It needs the highest revision for each drawing, and that is a maximum. A maximum does not care what order the rows are in.

With the helper column from section 5:

="R"&MAXIFS(Register!$C$2:$C$68,Register!$A$2:$A$68,A2)

Without one, in Microsoft 365:

="R"&MAX(FILTER(--TEXTAFTER(Register!$B$2:$B$68,"R"),Register!$A$2:$A$68=A2))

Both return R12, R11 and R10 for the three stale drawings. Shuffle the register, sort it backwards or add rows at the bottom, and the answers do not move.

Scenario: Replace the XLOOKUP in the issue sheet with the MAXIFS version and fill it down. Check that column C now matches column D for all eight drawings.

Try it in the grid

08Pad the Codes So Text Order Is Right

The other fix is at the source. Codes with a fixed number of digits sort correctly as text, because every character lines up: R02 before R10, as it should be.

="R"&TEXT(C2,"00")

That turns 2 into R02 and 12 into R12. Two-digit revision codes such as P01 and C02 are common on UK projects for exactly this reason. Padding only works to the width you chose: R100 would sort before R99 again.

Padding helps people and filters too. Anyone sorting the register by hand gets the right answer, with no helper column to remember.

Excel's own offer does not apply here. When you sort a column of numbers stored as text, such as "10" and "2", the Sort Warning offers to sort anything that looks like a number as a number. R10 does not look like a number, so the offer never appears.

09Check the Count Against the Revision

Revisions are issued in sequence, so a drawing's revision count and its highest revision number should be the same. A mismatch is a stale issue, visible before anything goes to site.

=COUNTIFS(Register!$A$2:$A$68,A2)=--TEXTAFTER(C2,"R")

With C2 holding the revision sent to site, this returns FALSE for S-101, S-103 and S-105. S-101 has 12 issues and was sent R9. The check needs no sort and no helper column.

If a project skips or withdraws revisions, compare against the helper-column maximum instead. Either way, put the check beside the transmittal rather than in a tab nobody opens.

Scenario: Add the COUNTIFS check in column F of the issue sheet, then put =COUNTIF(F2:F9,FALSE) above it. Harbrook's sheet returns 3, one for each drawing that went out stale.

Try it in the grid

10Six Traps

  1. Sorting codes as text. R10 sorts before R2, and the error appears only from the tenth revision.
  2. Last row as latest. XLOOKUP with -1 returns whatever the sort left at the bottom.
  3. MAX on codes. MAX and MAXIFS ignore text and return 0, not an error.
  4. A right answer on a short list. S-108 at R9 is correct, so a spot check passes.
  5. Padding too narrow. R01 to R99 sorts right; R100 breaks it again.
  6. Waiting for the Sort Warning. It only offers number sorting for cells that are entirely digits.

11Four Exercises

  1. Reproduce it. Type R1 to R12, sort A to Z and find where R10 lands.
  2. Fix the sort. Add =--TEXTAFTER(B2,"R") beside the codes and sort by that column instead.
  3. Skip the sort. Use MAXIFS on the helper column to return the current revision for each drawing.
  4. Prove it. Add the COUNTIFS check and confirm it flags S-101, S-103 and S-105 and nothing else.

What to Take Away

Harbrook's register held every revision. The sheet read it in dictionary order, and dictionary order puts 10 before 2. Nothing looked wrong until a drawing reached its tenth revision.

A code with a number in it is text. Sort and compare on the number, not the code, or pad the code so the two orders agree. Then check the count, because that is what would have stopped grid A–D.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge