Hundreds of open comments sorted into themes, with counts you made yourself

Course evaluations, customer feedback, staff surveys, event forms: the multiple-choice answers turn into a tidy chart, and the free-text answers get skimmed once and summed up by impression. “Mostly positive, a few complaints about the venue.” Then someone asks how many complained, and nobody knows.

Split the work: the assistant reads and sorts, which it does well, and your spreadsheet counts, which the assistant does badly. By the end of today you’ll have a one-page table with each theme, how many people raised it and a quote someone actually wrote, ready for a slide or a report. When the same survey comes back, you reuse the themes and the prompts and start halfway through.

Why “summarize these comments” is the wrong request

Ask for a summary of 300 comments and you’ll get something that reads well and has three weak spots. The numbers are guesses: language models are unreliable at counting long lists, and asking twice can give two different figures. Small groups disappear: four people who couldn’t read the slides get folded into “some logistical issues”, though they may be your most useful finding. And the “representative quotes” are often tidied versions of what someone wrote, or sentences nobody wrote.

So change the job. Ask for one label per comment, tied to an ID, and count the labels yourself. With 30 comments, skip all this and tally them by hand; the method pays off once there are more comments than you’d read carefully in one sitting.

Give every comment an ID

Make a tab called Comments, with headings in row 1, an ID in column A and the comment in column B. Form answers are most of the way there: in Google Forms, open Responses, then Summary, the More menu and Select destination for responses; in Microsoft Forms, open View responses and Open results in Excel (both checked in October 2026). Copy the comment column into column B. For the IDs, type 1 in A2 and 2 in A3, select both and drag the fill handle (the small square at the corner) down to the last comment.

To practice first, ask any assistant for a made-up set with a small complaint planted in it, so you can see later whether the method keeps it:

Write 60 made-up comments from a course evaluation for a one-day spreadsheet workshop. Vary them: one word to four sentences, a few typos, topics such as the trainer, the pace, the exercises, the handouts and the room. Include some that mix two topics, a few sarcastic ones and two that just say "n/a". Scatter exactly four that say the slides were hard to read from the back.

Return a table with two columns, ID (1 to 60) and Comment, and nothing else.

With real comments, remove names and anything else that identifies a person before you paste them; Before you upload a client document covers what to check about the tool.

Find the themes in a sample

Copy about 40 comments from different parts of the list, IDs included, into a new conversation:

Below are 40 open comments from a [course evaluation / customer survey / staff survey], each with an ID.

Propose 8 to 12 themes that together cover them. For each theme give a short name, a one-sentence definition of what belongs in it and what doesn't, and the IDs of two comments that fit it.

Keep a theme separate even if only two or three comments raise it. Don't merge small themes into bigger ones. Don't count anything.

[paste the comments here]

Then do the part only you can do. Merge overlaps (“Pace” and “Too much content” are often one complaint), split catch-alls (“Organization” can hide the room, the timing and registration), rename themes in your team’s words, add any theme you know matters, and add Other. Every count that follows depends on these definitions, so give them ten careful minutes. Save the final list with its definitions in a note, then type the names alone in a tab called Themes: the heading Theme in A1 and one name per row below it, Other included.

Give every comment one label

In a new conversation, paste the theme list and send the comments in batches of 50:

Here are my themes with their definitions:
[paste the themes]

Label each comment below. Return a table with these columns:
ID | Theme | Tone | Quote

- Theme: exactly one name from my list, or Other. Never invent a theme.
- Tone: positive, negative, mixed or neutral.
- Quote: up to 12 words copied exactly from the comment, typos included.
- One row per comment, same order, none skipped or merged.
- After the table, give the first and last ID and the number of rows.

[paste comments 1 to 50 here]

Long lists are where rows get skipped or merged, and the count at the end lets you check a batch of 50 at a glance. For the next one, write “Same instructions, next batch:” and paste comments 51 to 100. Paste each table into a tab called Labels, one below the other.

Count in the sheet

In the Comments tab, add the headings Theme, Tone and Quote in C1 to E1, then bring each label in next to its comment. In C2, then copied down:

=XLOOKUP(A2, Labels!A:A, Labels!B:B, "missing")

For tone in column D, change Labels!B:B to Labels!C:C; for the quote in column E, to Labels!D:D. “Missing” marks a comment the assistant skipped. XLOOKUP works in Google Sheets, Excel for Microsoft 365, Excel 2021 and later, and Excel for the web; in Excel 2016 or 2019, use =VLOOKUP(A2, Labels!A:D, 2, FALSE) and change the 2 to 3 for tone and 4 for the quote.

Now the counts. In the Themes tab, add the headings Comments, Positive and Negative in B1 to D1. Put these three formulas in B2, C2 and D2, one per cell, and copy them down:

=COUNTIF(Comments!C:C, A2)
=COUNTIFS(Comments!C:C, A2, Comments!D:D, "positive")
=COUNTIFS(Comments!C:C, A2, Comments!D:D, "negative")

Add up column B. The total must equal the number of comments. If it falls short, look in the Theme column of Comments for “missing”, a misspelled theme or one the assistant made up, and correct those cells by hand. If a formula won’t cooperate, describe the formula you need in plain words.

Check before you believe it

1. Every quote. In F2 of the Comments tab, put =ISNUMBER(SEARCH(E2, B2)) and copy it down. FALSE means the quote isn’t in the comment word for word: the assistant tidied it, so copy the real words from column B.
2. Everything in Other. Read it all. Five comments there about the same thing mean a missing theme.
3. Twenty random rows. Add =RAND() in column G, sort by it, and read the first 20 comments against their labels. If several miss the same way, sharpen that definition and relabel the batch. Watch the tone: “Great, another 8 a.m. start” can come back positive.

With the practice set, search column B for “slides” to find your four planted comments. They should share one theme; if they’re scattered or sitting in Other, tighten the theme definitions and label again.

The one-page table

Sort the Themes tab by the Comments column, largest first, and add one checked quote per theme in column E. For example, from the made-up workshop set:

Theme	Comments	Positive	Negative	Quote
Trainer	14	12	1	“explained formulas like a normal human”
Pace	11	2	8	“lost me after the break”
Exercises	10	7	2	“the budget exercise was the useful bit”
Room	9	3	5	“freezing by 3 p.m.”
Handouts	7	4	2	“will actually keep the cheat sheet”
Slides hard to read	4	0	4	“couldn’t read the slides from the back”
Other	5	1	1	“n/a”

Mixed and neutral comments make up the rest of each row, and the counts add up to 60.

Try it today

Pick one set of open answers: your last staff survey, the feedback forms from a recent event, the comment box of a customer survey, or the practice set above. Block an hour and note when you start. You’re done when one page holds your themes, counts that add up to the total and a checked quote for each, pasted where the results will be presented. Keep the theme list and both prompts with the file; next round you start at the labeling step and can compare your time with today’s.

Where to take it next

Next comes telling people what you found. Write the headline of every slide before you design any of them turns each row into a headline that states the finding, such as “Four people couldn’t read the slides from the back”. If the survey comes back every month, a monthly summary you build once and refresh each month sets up the counting once, so next month you paste in the newly labeled comments and the summary follows. If you teach, the themes show where people struggled, and ten good questions from your own material turns them into a quiz for the next group.