Counting in Excel
 

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

1. Suppose you have the numbers in ColA. Assign a header to the column.
Select the range in Col A including the header.
2. From menu DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list in Col D

If this post helps click Yes
---------------
Jacob Skaria


"mrsmasterg" wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

=SUMPRODUCT((FREQUENCY(A1:A10,A1:A10)0)*1)

Returns 2 with your sample data.


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 11:52:19 -0700, mrsmasterg
wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

My apologies. It did not work on my original problem. I did not mention
that the numbers contain dashes (social security numbers). Does this make a
difference?

Thanks for your tip, though! I was able to apply it to another problem that
contained only numbers. Do you have any other suggestions since I've
provided additional information?



"Gord Dibben" wrote:

=SUMPRODUCT((FREQUENCY(A1:A10,A1:A10)0)*1)

Returns 2 with your sample data.


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 11:52:19 -0700, mrsmasterg
wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

Thanks for the great tip! It's not quite what I'm looking for to solve this
particular problem, however, it's something I can definitely use.

"Jacob Skaria" wrote:

1. Suppose you have the numbers in ColA. Assign a header to the column.
Select the range in Col A including the header.
2. From menu DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list in Col D

If this post helps click Yes
---------------
Jacob Skaria


"mrsmasterg" wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

Numbers or text........

This formula is from Chip Pearson's site.

http://www.cpearson.com/excel/Duplicates.aspx

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)0,MATCH(A1:A10,A1 :A10,0),""),
IF(LEN(A1:A10)0,MATCH(A1:A10,A1:A10,0),""))0,1))

An array formula so must be entered using CTRL + SHIFT + ENTER


Gord


On Thu, 17 Sep 2009 15:56:01 -0700, mrsmasterg
wrote:

My apologies. It did not work on my original problem. I did not mention
that the numbers contain dashes (social security numbers). Does this make a
difference?

Thanks for your tip, though! I was able to apply it to another problem that
contained only numbers. Do you have any other suggestions since I've
provided additional information?



"Gord Dibben" wrote:

=SUMPRODUCT((FREQUENCY(A1:A10,A1:A10)0)*1)

Returns 2 with your sample data.


Gord Dibben MS Excel MVP

On Thu, 17 Sep 2009 11:52:19 -0700, mrsmasterg
wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

I would like to accomplish this same thing in a pivot table. Any advice on
doing that - if I can piggy-back on your question? :)

Christa

"mrsmasterg" wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

Counting in Excel
 

So you're looking to count unique entries?

Maybe using this technique from Debra Dalgleish's site would work:
http://contextures.com/xlPivot07.html#Unique



christabbie wrote:

I would like to accomplish this same thing in a pivot table. Any advice on
doing that - if I can piggy-back on your question? :)

Christa

"mrsmasterg" wrote:

I have a spreadsheet in which duplicate data appears multiple times in
groups. I would like to count each occurence. Ex.
12345
12345
12345
12345
78912
78912
78912
78912
78912

expected result is that each occurence is counted once.
12345 occurs once
78912 occurs once

I do not want to use a pivot table because each 'group' has multiple rows of
data that needs to be shown.

--

Dave Peterson