How do I check an anchor rod survey against the anchor rod plan with AI before steel erection?

Read this anchor rod survey [upload CSV or PDF] and this anchor rod plan schedule [paste: column mark, design center offset X and Y from grid, rod count, rod pattern, required projection]. Build a Survey table with one row per rod: column mark as written, rod number, X, Y, top-of-rod elevation, top-of-concrete elevation. Copy values exactly and write NOT SHOWN instead of guessing, and propose a column match with a confidence word (clear, likely, unsure) for any unclear shot. Do not supply tolerances from memory and do not decide whether a rod is acceptable.

EXAMPLE DATA ONLY. Here are my Design, Tolerances and Survey tabs [paste the example rows]. Write Excel formulas (or a step-by-step Copilot plan) that count rods per column, compute the measured group center as the average rod X and Y, the center offset from the design center including any design offset, each rod's deviation from its pattern position about the measured center, and projection in inches, and flag anything outside my tolerances, columns with no survey, survey points with no design column, and rod-count mismatches (skip geometry flags for those). Use only my data and tolerances.

Design tab (example; X, Y in inches from the grid intersection)
Column | Design center X | Design center Y | Rods | Pattern | Required projection (in)
A-1 | 0 | 0 | 4 | square, rods at +/-3 in | 7.00
A-2 | 0 | 0 | 4 | square, rods at +/-3 in | 7.00
B-1 | 0 | 0 | 4 | square, rods at +/-3 in | 7.00
B-2 | +2.00 | 0 | 4 | square, rods at +/-3 in | 7.00
C-1 | 0 | 0 | 4 | square, rods at +/-3 in | 7.00
C-2 | 0 | 0 | 6 | X +/-3 in, Y -6/0/+6 in | 9.00

Tolerances tab (placeholders I typed, NOT from any standard)
Group center 0.40 in | Rod to pattern 0.20 in | Projection +/-0.75 in

Survey tab (example; rods listed SW, SE, NE, NW; elevations in feet)
Column | Rod | X | Y | Top of rod | Top of concrete
A-1 | 1 | -2.95 | -3.05 | 100.585 | 100.00
A-1 | 2 | 3.05 | -2.95 | 100.580 | 100.00
A-1 | 3 | 3.00 | 3.05 | 100.585 | 100.00
A-1 | 4 | -3.00 | 2.95 | 100.580 | 100.00
A-2 | 1 | -2.45 | -2.80 | 100.585 | 100.00
A-2 | 2 | 3.55 | -2.85 | 100.585 | 100.00
A-2 | 3 | 3.50 | 3.20 | 100.590 | 100.00
A-2 | 4 | -2.50 | 3.15 | 100.585 | 100.00
B-1 | 1 | -3.00 | -3.00 | 100.585 | 100.00
B-1 | 2 | 3.00 | -3.00 | 100.585 | 100.00
B-1 | 3 | 3.35 | 3.00 | 100.585 | 100.00
B-1 | 4 | -3.00 | 3.00 | 100.585 | 100.00
B-2 | 1 | -1.00 | -3.00 | 100.520 | 100.00
B-2 | 2 | 5.00 | -3.00 | 100.515 | 100.00
B-2 | 3 | 5.05 | 3.00 | 100.520 | 100.00
B-2 | 4 | -1.00 | 3.05 | 100.520 | 100.00
C-2 | 1 | -3 | -6 | 100.700 | 99.95
C-2 | 2 | 3 | -6 | 100.700 | 99.95
C-2 | 3 | 3 | 0 | 100.705 | 99.95
C-2 | 4 | -3 | 0 | 100.700 | 99.95
C-2 | 5 | -3 | 6 | 100.700 | 99.95
D-3 | 1 | -3 | -3 | 100.585 | 100.00
D-3 | 2 | 3 | -3 | 100.585 | 100.00
D-3 | 3 | 3 | 3 | 100.585 | 100.00
D-3 | 4 | -3 | 3 | 100.585 | 100.00

EXAMPLE DATA ONLY (not from a real project). Design tab: A-1 | center 0,0 | 4 rods | 6 in square pattern | projection 7.00 in; A-2, B-1, C-1 the same; B-2 | center +2.00 in X, 0 Y | 4 rods | 6 in square | 7.00 in; C-2 | center 0,0 | 6 rods | 6 in by 12 in pattern | 9.00 in. Tolerances tab (placeholder numbers I typed, not from any standard): group center 0.40 in, rod-to-pattern 0.20 in, projection plus or minus 0.75 in. Survey tab: one row per rod with X, Y in inches from the grid intersection and top-of-rod and top-of-concrete elevations in feet. Write Excel formulas (or a step-by-step Copilot plan) that compute rod count, measured group center, center offset from the design center including the design offset, each rod's deviation from its pattern position, and projection in inches, and flag anything outside my tolerances, plus columns with no survey and survey points with no design row. Use only my data; do not decide whether a rod is acceptable.

Expected flags on this example (illustrative — verify every formula yourself)
A-1: center (0.025, 0.000), offset 0.03 in; worst rod 0.06 in; projection 6.96 to 7.02 in → no flag
A-2: center (0.525, 0.175), offset 0.55 in > 0.40 → GROUP CENTER OFFSET (whole group shifted; rods are fine relative to each other, 0.04 in)
B-1: center (0.088, 0.000), offset 0.09 in; rod 3 (NE) is 0.26 in from its pattern position > 0.20 → ROD OUT OF PATTERN
B-2: center (2.013, 0.012) vs design (+2.00, 0), offset 0.02 in → no center flag. If the +2.00 in design offset is left off the Design tab, this column falsely shows a 2.01 in offset, which is the classic mistake. Projection (100.515 to 100.520 − 100.00) × 12 = 6.18 to 6.24 in vs 7.00 → SHORT PROJECTION on all four rods (0.76 to 0.82 in short)
C-1: on the plan, no survey rows → NOT SURVEYED
C-2: 5 rods surveyed vs 6 on the plan → ROD COUNT MISMATCH (the NE rod is missing from the survey or the field). Do not trust center or pattern checks for this group until the missing rod is resolved; with one rod missing the average center moves to (−0.60, −1.20) and every rod looks 1.34 in off. Projection 9.00 to 9.06 in → OK
D-3: survey rows with no Design row → NO DESIGN COLUMN (wrong label, or a plan revision the sheet does not have)

Suggested Excel logic (write against your own sheet layout)
- Count: COUNTIF(Survey[Column], Design[Column]) vs Design[Rods].
- Measured center: AVERAGEIF(Survey[Column], col, Survey[X]) and the same for Y.
- Center offset: SQRT((MeasX − DesignX)^2 + (MeasY − DesignY)^2), flag when > your group center tolerance.
- Rod deviation: SQRT(((X − MeasX) − PatternX)^2 + ((Y − MeasY) − PatternY)^2) using a Pattern tab keyed on column and rod number; flag when > your rod tolerance.
- Projection (in): (TopOfRod − TopOfConcrete) × 12; flag when ABS(projection − required) > your projection tolerance.
- Skip center and pattern flags when the count check fails, and say so in the row.

Same check as a short Python script (our own code; reads two CSVs you export)
import csv, math
from collections import defaultdict
TOL_CENTER, TOL_ROD, TOL_PROJ = 0.40, 0.20, 0.75   # type yours from your contract documents
design = {r["Column"]: r for r in csv.DictReader(open("design.csv"))}
pattern = defaultdict(dict)   # pattern.csv: Column,Rod,PX,PY
for r in csv.DictReader(open("pattern.csv")):
    pattern[r["Column"]][r["Rod"]] = (float(r["PX"]), float(r["PY"]))
rods = defaultdict(list)
for r in csv.DictReader(open("survey.csv")):
    rods[r["Column"]].append(r)
for col in sorted(set(design) | set(rods)):
    if col not in design: print(col, "NO DESIGN COLUMN"); continue
    if col not in rods: print(col, "NOT SURVEYED"); continue
    d, rs = design[col], rods[col]
    for r in rs:
        proj = (float(r["TopRod"]) - float(r["TopConc"])) * 12
        if abs(proj - float(d["Proj"])) > TOL_PROJ: print(col, "rod", r["Rod"], f"PROJECTION {proj:.2f} in")
    if len(rs) != int(d["Rods"]): print(col, f"ROD COUNT {len(rs)} vs {d['Rods']}, skip geometry"); continue
    mx = sum(float(r["X"]) for r in rs) / len(rs); my = sum(float(r["Y"]) for r in rs) / len(rs)
    off = math.hypot(mx - float(d["CX"]), my - float(d["CY"]))
    if off > TOL_CENTER: print(col, f"GROUP CENTER OFFSET {off:.2f} in")
    for r in rs:
        px, py = pattern[col][r["Rod"]]
        dev = math.hypot(float(r["X"]) - mx - px, float(r["Y"]) - my - py)
        if dev > TOL_ROD: print(col, "rod", r["Rod"], f"OUT OF PATTERN {dev:.2f} in")
Do not mark any rod accepted or rejected — only flag for human review.