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]

That's because you're still looking at column A, if you are counting duplicates of ID, change the range in the formula:

=COUNTIF($E$2:E2,E2)
Copy down.

 

[jd222cu]

That's because you're still looking at column A, if you are counting duplicates of ID, change the range in the formula:

=COUNTIF($E$2:E2,E2)
Copy down.

Thanks for the quick reply. That's not it. My formula is

=IF(COUNTIF($J$2:J2,J2)=1,COUNTIF(J:J,J2),"")

There are titles in the first row and ID is in column J (I hid a few columns when posting) and I dragged down.

Could it be ID is too large so it only compares the first few values or something. I created ID using the & operator and several fields including a date field and a number field. Also I think ID is stored as text but I tried converting to nos using "+0" and that didn't work either

 

[Scott Huish]

For your problem, try this instead:

=IF(SUMPRODUCT(--($J$1:J1=J1))=1,SUMPRODUCT(--(J:J=J1)),"")

Copy down.

If you are using a version of Excel prior to XL2007, you will need to specify an exact range in the second SUMPRODUCT instead of J:J (any range less than or equal to 65535 rows)

I think the problem is that Excel is trying to interpret that as a number and it is too big for Excel to handle in COUNTIF.

 

[jd222cu]

Thanks for the quick reply. That's not it. My formula is

=IF(COUNTIF($J$2:J2,J2)=1,COUNTIF(J:J,J2),"")

There are titles in the first row and ID is in column J (I hid a few columns when posting) and I dragged down.

Could it be ID is too large so it only compares the first few values or something. I created ID using the & operator and several fields including a date field and a number field. Also I think ID is stored as text but I tried converting to nos using "+0" and that didn't work either

I researched it and the issue seems to be 15 digits of precision. Excel treats number stores as a text as a number only upto 15 digits of precision.

For reference I solved it by appending an "a" to all the digits so that it would be really treated as text. I think a pivot would work too. Thanks for the help.

 

[jd222cu]

For your problem, try this instead: =IF(SUMPRODUCT(--($J$1:J1=J1))=1,SUMPRODUCT(--(J:J=J1)),"") Copy down. If you are using a version of Excel prior to XL2007, you will need to specify an exact range in the second SUMPRODUCT instead of J:J (any range less than or equal to 65535 rows) I think the problem is that Excel is trying to interpret that as a number and it is too big for Excel to handle in COUNTIF.

Thanks! On a deadline now but will dissect and try this later. Again appreciate the quick responses.

 

[C_Dennis]

Thank you, this worked.

 

[Tyron]

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]5 = 3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1 = 2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I like your solution, but for me it only takes care of half of the solution.

Is there a way to also display the item and count in another location of the spreadsheet? Above is an example:

Now in Column C1,C2,C3 I would like to put a formula that first identifies the name of the duplicate (in this case "5") followed by "=3" which indicates the quantity of that particular duplicate. The reason for C1,C2, and C3 is I usually have up to three duplicates.

I am trying to avoid using array formulas or creating a function, but if there is no other way I am willing to try.

Thanks in advance.

Tyron

COUNTIF should work:

Assuming your data starting in A1:

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

Copy down.

Sheet2

*	A	B
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]200101[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]200101[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]200101[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]200102[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]200102[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]200103[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Tyron]

Hey Guys,

After browsing through the site and doing a bit of newbie reading I found that I left out some pertinent information.

I am using Windows 7 and Excel 2013. Sorry about that.

Let me know if I have forgotten anything else.

later

Ty

 

[romelsms1]

dear all,

on the same idea I want to find the duplicates on the same rows, in detail I have two row (with numbers) and I want to count the similar values on the same row.
the easy way will be to put in another column a IF condition to return 1 if the values are similar and to sum it but I don't want to use another column.
Can you help me with a formula?
A B
1 1 identical
2 1
1 2
3 3 identical

answer will be 2

 

[Scott Huish]

=sumproduct(--(a1:a100=b1:b100),--(a1:a100<>""))