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?

 

[CarolynR]

The duplicate student numbers have no spaces or missing digits. If any numbers were not 9 digits long or had spaces, the numbers would sort to the top or the bottom of the number sorted list, and none have.

Maybe it would help if you explained what the formula:
=IF(COUNTIF($E$1:E1,E1)=1,COUNTIF(E:E, E1),"")
actually is supposed to do. If the first result of the formula in Col C, Row 1 = 1, then wouldn't "1" appear in this cell?

 

[Scott Huish]

I'll show my example, showing it with the formula copied down.

In the first COUNTIF, we are using $E$1:E1 so when it is copied down to the next cell this part will become $E$1:E2, so it is checking to see if that is the first occurence of that value up to that point, and if so, will put the count of that value from all of Column E there, otherwise returns blank ""

Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200101</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200101</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200101</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200102</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200102</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">200103</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=IF(COUNTIF($E$1:E1,E1)=1,COUNTIF(E:E,E1),"")</TD></TR><TR><TD>C2</TD><TD>=IF(COUNTIF($E$1:E2,E2)=1,COUNTIF(E:E,E2),"")</TD></TR><TR><TD>C3</TD><TD>=IF(COUNTIF($E$1:E3,E3)=1,COUNTIF(E:E,E3),"")</TD></TR><TR><TD>C4</TD><TD>=IF(COUNTIF($E$1:E4,E4)=1,COUNTIF(E:E,E4),"")</TD></TR><TR><TD>C5</TD><TD>=IF(COUNTIF($E$1:E5,E5)=1,COUNTIF(E:E,E5),"")</TD></TR><TR><TD>C6</TD><TD>=IF(COUNTIF($E$1:E6,E6)=1,COUNTIF(E:E,E6),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

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

 

[CarolynR]

Hi Hotpepper,

I think I may have figured out what is happening! I have a header row of titles in my first row of data, so when I changed the formula in Col C to:

=IF(COUNTIF($C$2:C2,C2)=1,COUNTIF(C:C,C2),"")

I don't get the "1" in my formula row at the first occurance of the duplicate number, just the correct total!

Is there a way to get rid of the error message in every Col C cell that states "Formula refers to empty cells" (I've already set "Row and Column Headers" in Tools - Options - View and page setup)?

 

[Scott Huish]

In pre-XL2007, I think it is under Tools, Options. I think there is a tab that has Error Checking rules, uncheck Formulas referring to empty cells.

 

[CarolynR]

Hi Hotpepper,

Thanks - I turned off the checkbox for:
Tools - Options - Error Checking - Formulas referring to Empty Cells
and all is working well.

Thank you for all of your time!

Hi Kaps - I'm working through the Pivot Data tutorial and will soon use a sample set of my data - very exciting!

 

[antoanand1981]

I am looking for some more help over here, I am just tyring to do little bit more in the below format.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">200101</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">200101</TD><TD> 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">200101</TD><TD> 3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">200102</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">200102</TD><TD> 2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">200103</TD><TD style="TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>
Bascially I am looking for the dupliate count

 

[antoanand1981]

COUNTIF should work:

Assuming your data starting in A1:

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

Copy down.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">200101</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">200101</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">200101</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">200102</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">200102</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">200103</TD><TD style="TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF(A:A,A1),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

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

For Example How do we get the numbers for the same above like repeat of 1,2,3 in column as a series

 

[Scott Huish]

=COUNTIF($A$2:A2,A2)
Copy down.

Excel Workbook
	A	B
2	200101	1
3	200101	2
4	200101	3
5	200102	1
6	200102	2
7	201003	1
Sheet1

 

[antoanand1981]

Thanks

 

[jd222cu]

=COUNTIF($A$2:A2,A2)
Copy down.

Sheet1

*	A	B
2	200101	1
3	200101	2
4	200101	3
5	200102	1
6	200102	2
7	201003	1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =COUNTIF($A$2:A2,A2) B3 =COUNTIF($A$2:A3,A3) B4 =COUNTIF($A$2:A4,A4) B5 =COUNTIF($A$2:A5,A5) B6 =COUNTIF($A$2:A6,A6) B7 =COUNTIF($A$2:A7,A7) <tbody> </tbody>

<tbody>
</tbody>

Hi

I am trying it with this large unique id I created and its not working? any idea why?

Store	Date Scanned	NDC	Qty	ID	Duplicates
8896	09-Jan-13	74455290	3	889674455290412833	3
8896	24-Dec-12	74455290	1	889674455290412671
8896	10-Dec-12	74455290	2	889674455290412532

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

e.g. the above 3 rows are being counted as duplicates. any help would be greatly appreciated. btw the duplicate function is off the ID column