Count duplicate rows and produce count #

[CarolynR]

I have a spreadsheet of over 15,000 lines of student information, sorted by student number. I want to count the number of rows which have a duplicate student number, up to 15 duplicates in a row, and show the total number of duplicates in a Separate Column. I.e.

Column 1 Column 2
Row 1 - 200101 3
Row 2 - 200101
Row 3 - 200101
Row 4 - 200102 2
Row 5 - 200102
Row 6 - 200103 1

I've been trying to use a Countif formula, but I found I had to use so many ANDs and ORs that the formula became too long. I don't know how to use programming code, only formulas in Excel. Is there an easier solution using some type of SUMPRODUCT code?

 

[Scott Huish]

COUNTIF should work:

Assuming your data starting in A1:

=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1),"")

Copy down.

Excel Workbook
	A	B
1	200101	3
2	200101
3	200101
4	200102	2
5	200102
6	200103	1
Sheet2

 

[CarolynR]

Thanks for the formula, Hot Pepper! Except it produces 2 counts for each duplicate student, with 1 in the first occurance, then the total count in the second occurance, i.e.:

200101 1
200101 6
200101
200101
200101
200101
200102 1
200102 3
200102
etc.

 

[Scott Huish]

It shouldn't, as you can see in my example.

What range is your data in? Can you post the formula as you have it?

Are there any extraneous spaces in your data?

 

[Kaps_mr2]

Ever thought of using a Pivot table ? Kaps

 

[CarolynR]

Hotpepper:

My formula is viewing the student numbers in Column E (and producing results in column C), so my formula is:

=IF(COUNTIF($E$1:E3,E3)=1,COUNTIF(E:E, E3),0)

Hi Kaps - I've never used a pivot table - are there online instructions on how to use one?

 

[Scott Huish]

If you're starting from row 1, the first formula should be:

=IF(COUNTIF($E$1:E1,E1)=1,COUNTIF(E:E, E1),"")

 

[Kaps_mr2]

Carolyn,

Look at ...

http://www.homeandlearn.co.uk/ME/mes9p4.html

Regards

Kaps

 

[CarolynR]

Hi Hotpepper,

Yes, my first cell (in Column C, row 1) was the formula you suggested, and yes, it still produces a 1 in the first occurance of the duplicate number, then the correct total duplicate number in the second occurance of the duplicate number.

 

[Scott Huish]

Assuming you have the correct cell references, it shouldn't do that unless there is something different between the two such as the second one has a space after it and the first one doesn't, for example. If it's column C now, you should change the E references to C.