How many times a name is written before?

[Ipinho100]

Hi

I have an Excel sheet where is almost a million rows. I have different names listed in column U. I am trying to find out how many times a name has been written above and i have a following formula: countif($U$1:U1;U2). The problem is that Excel gets stuck because every time it has to go through all the rows from beginning. How can i do this easier?

[shg]

Pivot table?

[FDibbins]

how many times a name has been written above

above what?

[Tony Valko]

above what?

Here's what they're doing...

Data Range

	A	B
1	Name1	1
2	Name1	2
3	Name5	1
4	Name1	3
5	Name2	1
6	Name3	1
7	Name4	1
8	Name1	4
9	Name5	2
10	Name1	5
11	------	------

This formula entered in B1 and copied down:

=COUNTIF(A$1:A1,A1)

However, they said they have about 1,000,000 rows of data.

So, if you copy that formula to 1,000,000 rows this is how many cells you've referenced:

=SUMPRODUCT(ROW(1:1000000))

[Tony Valko]

=SUMPRODUCT(ROW(1:1000000))

That formula won't work in Excel versions 2003 and earlier!

[Ipinho100]

Thank you for the clearance, that is the problem...

[Tony Valko]

Unfortunately, I can't think of a better (more efficient) formula.

Maybe you could copy the formula down so many rows then convert those to constants and repeat this until you've reached the end of the range.

[Ipinho100]

That is not the only column where i have the same problem, so it gets a bit too heavy to do it manually... If there only would be another way to do it?!!

[shg]

What's the purpose of the formula, why do you need it?