How to Clean Up a Messy Group Order Spreadsheet

The file you collected is not the file you can pack from. Free-text sizes, siblings on one row, three spellings of the same teacher's name, and a header row buried under a logo image. Cleanup is unavoidable; doing it in the wrong order is what makes it painful.

Clean in this order

1. 1Structure - one header row, one row per item or per order, no merged cells.
2. 2Identity - a single reliable person name field, duplicates resolved.
3. 3Products - consistent style, color, size, and quantity values.
4. 4Groups - classroom, team, grade, or pickup location filled in for every row.
5. 5Verification - totals reconciled against what you actually ordered.

Fixing groups before identity means re-fixing groups after you merge duplicates. Fixing products before structure means formulas break when you unmerge cells. Order matters.

Step 1: Structure

• Delete logo rows, notes rows, and blank spacer rows above the header. The header must be row 1.
• Unmerge everything (Format > Merge cells > Unmerge), then fill the values down.
• Decide your row shape: one row per item is easier for packing; one row per order needs a column per style.
• Remove totals rows at the bottom. They will import as a phantom order named 'TOTAL'.
• Strip currency symbols and stray spaces from numeric columns so quantities stay numbers.

Step 2: Identity and duplicates

You need one field that identifies the human the bag goes to. For schools, that is the student, not the parent who paid.

• Split combined name columns into first and last, then build a single display name you control.
• Standardize capitalization with PROPER() so sorting groups the same person together.
• Sort by last name and eyeball the neighbors. Real duplicates sit next to each other.
• Distinguish true duplicates (same person submitted twice) from same-name siblings or cousins. Same name plus different classroom is usually two people.
• For a genuine double submission, keep one row and add a note. Do not silently delete a paid order.

Step 3: Products and sizes

Free-text sizes are the number one cause of mis-packed bags. Normalize them before anyone touches a box.

• Map every variation to one canonical value: YS, YM, YL, S, M, L, XL, 2XL. 'Med', 'medium', and 'M' must all become M.
• Keep youth and adult sizes visibly distinct. YL and L look similar at a folding table under bad lighting.
• Split style and color into separate columns if they were collected together.
• Default missing quantities to 1 only when the row clearly represents one item; otherwise flag it.
• Flag any size that does not exist for that style - somebody ordered a youth size of an adult-only hoodie.

Step 4: Groups and pickup

• Every row needs a group value. Blank classroom means the bag has nowhere to go on pickup day.
• Standardize teacher names to one format: last name only, or 'Last, First' - pick one.
• Create an explicit 'Unassigned' group rather than leaving blanks, so the count is visible.
• If you have multiple pickup locations, add a location column now, not later.

Step 5: Verify before you pack

1. 1Total item count in the file must equal the total you ordered from the supplier. If not, find the difference now.
2. 2Count distinct people. Compare that against the number of bags you expect to fill.
3. 3Pivot by style, color, and size and compare against the purchase order line by line.
4. 4Save this cleaned file as your source of record and stop editing the original.

Make next season easier

• Change the collection form: required dropdowns for size and classroom eliminate most of this work.
• Keep your column names identical year to year so a saved import mapping keeps working.
• Store the cleaned file with the event so anyone can audit it later.

Once the file is clean, the next steps are labels and pickup day.