Hello,



I have a spreadsheet with 270,000 words. Some words appear 1 or 2 times, and some appear 10,000. There are probably between 100,000 and 150,000 unique values.

I would like to get a count of each word. I have used this "=COUNTIF($A$1:$A$272145,A1)" formula, but it takes about two hours to calculate.

I am hoping someone has a suggestion as to how I can do this quicker.

I would sincerely appreciate it

Interesting. Have you tried sorting the rows? I don't know whether that would take longer or not. If you can sort the data, then afterward you can change the countif to something like this in column B:

If I haven't bobbled the syntax, that'll list the word count for each word in the last row (rather than every row) where the word appears—and I expect it'll work a lot faster than a COUNTIF.

This didn't work for me.

I do have the column sorted A-Z so that all like values are together (column A). I would like for Column B to tell me how many times the corresponding word in column A appears.

so my column A looks like:

alphabet
alphabet
alphabet
banana
banjo
banjo
crayon
crayon
crayon
crayon

I would like my column B to look like:

3
3
3
1
2
2
4
4
4
4

I also could work with a response like this:

alphabet 3
banana 1
banjo 2
crayon 4


I don't mind if it tells me each time the word appears, or only once per word, but since there are so many words, I cannot do an individual count.

I hope this makes it a bit clearer.

I'm not sure whether you noticed, but my solution does give you a count for each word; the problem is that it doesn't give you that count on every row, only on the last row for each word. You can change it around to have it give you the count on the first row of every word, if you prefer.

Here's what's going on: COUNTIF works, alright, except that there are 270 000 rows and therefore 270 000 COUNTIFs, and each one of those 270 000 COUNTIFs has to count all 270 000 rows. That's why it's taking so long. Let's call that solution #1.

Once it's sorted, using the code I gave you—call that solution #2—should work a lot faster. But instead of the answer laid out the way you wanted, it would look like this:

Now, there are two other ways I can think of to do this:

3) In column C use the formula I gave you: "=IF($A2=$A1,$C1+1,1)". Then in column B, use this: "=IF($A2=$A3,$B3,$C2)". Column C counts up the number of each word, just as with my formula in solution #2. But column B looks at column C to display the word count on every row, the way you have it in solution #1; it just works a lot faster. At least, I think it will; I didn't test it with 270K rows.

4) Write a VBA program. This is probably the way I would have done it in the first place, because I'm a programmer at heart. The nice thing about this solution is that it'll work fast and you don't have to sort the words first. Also it can put the the answers in a separate sheet, with one row for each word, as you suggested. The only wrinkle is that you have to be able to write a little program. We can show you how, but you don't want to just copy it; you pretty much have to take the time to learn how it works.

this works for you IN THE SENSE that it gives you a count of each word—only it's not on every row, just on the last row for each word. You could make it on the first row, if you prefer. The problem with your list is that if COUNTIF

Have you tried Data - Subtotal ? ( I don't know if it will accomodate that many lines though)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post