Organising large data sets

Turn messy spreadsheets and exports into answers you can trust.

Ask the questionCan AI turn 760 rows of bank export into one tidy sheet per tenant? Yes. It matched the bank to the cent.

Every business is sitting on data: customer lists, invoices, job histories, stock sheets, website enquiries. Most of it lives in a spreadsheet that three people have edited three different ways. AI is genuinely brilliant at this kind of clean-up, as long as you do it in the right order and check its work.

1. Open your spreadsheet in Google Sheets. Make a copy first: File, then Make a copy. Never work on the original.
2. In the copy, delete or replace any columns with names, emails, phone numbers or addresses. Put a simple ID number in their place if you need it.
3. Choose File, Download, then Comma-separated values. This saves a CSV file.
4. Open claude.ai, start a new chat, and attach the CSV file.
5. Paste the data clean-up prompt from this guide and send it. Claude will first describe the problems it finds, without changing anything.
6. Read the list. Tell Claude which fixes to make, and how, for example "use day, month, year for dates".
7. Ask for the cleaned file back as a CSV, plus a short list of every change it made.
8. Ask: "How many rows did you read, and how many are in the cleaned file?" Check that against your original.
9. Import the cleaned CSV into a new tab in Google Sheets: File, Import, Upload, then Insert new sheet.
10. Pick three numbers Claude reported and check them yourself with a filter or a SUM formula.

Step 1: strip the personal stuff first

Before anything goes into an AI tool, remove what it doesn't need: names, phone numbers, emails, addresses, anything health or financial. Replace them with an ID number if you need to match rows up later. You usually don't need to know who someone is to find out which suburb your best jobs come from. Check your plan's privacy settings too, and your own privacy policy.

Step 2: clean the columns

Messy data is usually the same few problems: dates written five ways, suburbs spelled wrong, "$1,200" sitting next to "1200.00", one column holding three things, and the same customer entered twice. Ask Claude to look at a sample first and tell you what's wrong before it fixes anything. Then fix one problem at a time.

Step 3: dedupe carefully

Duplicates are rarely exact. "Bob's Plumbing" and "Bobs Plumbing Pty Ltd" are probably the same business. Ask Claude to list the likely duplicates and why it thinks so, then you decide. Never let it silently merge rows.

Step 4: ask real questions

Once it's clean, ask the questions you actually care about. Which services make the most money? Which months are quiet? Which customers haven't been back in a year? Where do my enquiries come from? Claude can analyse an uploaded file and make a chart.

Step 5: check the answers

This step is the one people skip. AI can add up wrong, miss rows or confidently misread a column. Spot-check: pick three numbers it gave you and check them yourself in the spreadsheet with a simple SUM or a filter. Ask it how many rows it read and compare that to your file. If anything's off, say so and ask it to redo it.

A real one: a bank export into 35 tenant sheets

A local business I work with rents out stalls to about 35 sellers. Rent comes in by bank transfer, and the owner wanted to know who had paid what. All he had was the bank's export: 760 rows across 16 months, rent mixed in with transfers, interest and everything else. Doing it by hand meant a weekend with a highlighter.

The hard part wasn't the maths. It was the names. The bank cuts names off and writes them however the payer typed them, so one tenant can show up three or four ways: "J SMITH", "MS JANE A SMITH", "SMITH J SHOP 12". Sort by name and that one tenant becomes three people, each looking like they've only paid a third of their rent.

1. Find the problems first. I had Claude list every distinct payer name and group the ones it thought were the same person, with its reason for each.
2. The owner decides the merges. The grouping went to the owner as a short list to confirm. Anything uncertain stayed separate until he said yes. A wrong merge means telling a tenant they owe money they've already paid.
3. Save the merges as a list. Every confirmed variant went into a name list, so next month's export sorts itself and only new spellings need a human.
4. One sheet per tenant. The result was a workbook with a summary tab and a sheet for each tenant, payments in date order with a total, plus separate tabs for outgoing transfers and interest.
5. Check it adds up. The summary had to match the bank's own income total to the cent, and it did. That's the step that makes it trustworthy.

That one-off clean-up later became a small rent tracker. Each month the owner uploads the new export: the first month, 28 of 33 payments matched a tenant on their own, and the 5 it couldn't place came back as questions for the owner, not guesses. Same lesson as the rest of this chapter: let AI do the sorting, and keep the judgement calls with a person.

I've attached a bank export. I want every payment from each [tenant / customer] on its own sheet, in date order, with a total.

Before you sort anything:
1. Tell me how many rows you see and the date range.
2. List every distinct payer name. Group the ones you think are the same person or business, and give your reason for each group.
3. Mark any group you're not sure about. Do not merge those until I confirm.
4. List the rows that aren't customer payments (transfers out, interest, fees) so I can check them.

Once I've confirmed the groups, build the sheets and a summary tab. The summary's total income must match the export's total income exactly. Tell me both numbers.

I've attached a spreadsheet from my business. It's [what the data is, e.g. a year of job records]. Each row is [what one row means].

Before you change anything:
1. Tell me how many rows and columns you see, and what each column seems to hold.
2. List every data quality problem you find: inconsistent dates, misspellings, mixed number formats, blank cells, columns holding more than one thing, likely duplicates. Give a few examples of each.
3. Suggest a fix for each problem and ask me to confirm before you apply it.

Do not merge or delete any rows without asking me. If you're unsure what a column means, ask.