count duplicates/total number of occurances
 

I need to create a report showing the total list of people and the occurances.

Currently my list looks like the following:

A1
ADAM BROWN
ADAM BROWN
JOHN SMITH

I need a report showing me the name and the total number of occurances e.g.

A1 B1
ADAM BROWN 2
JOHN SMITH 1

Please advise on how this could be done?

count duplicates/total number of occurances
 

I think this is the easiest way:

Select your list
DataPivot Table
Source: Excel List...Click Next
Range: (already selected)...Click Next
Click the [Layout] button
ROW: Drag the Name field here
(Dbl-click it and set subtotals to None)

DATA: Drag the Name field here
(If it doesn't change to Count of Name: Dbl-click it and set COUNT)

Click [OK]
Select a destination then click [Finish

That will create the kind of list you're looking for.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rishi Aggarwal" wrote:

I need to create a report showing the total list of people and the occurances.

Currently my list looks like the following:

A1
ADAM BROWN
ADAM BROWN
JOHN SMITH

I need a report showing me the name and the total number of occurances e.g.

A1 B1
ADAM BROWN 2
JOHN SMITH 1

Please advise on how this could be done?

count duplicates/total number of occurances
 

This is awesome !! Thanks.

"Ron Coderre" wrote:

I think this is the easiest way:

Select your list
DataPivot Table
Source: Excel List...Click Next
Range: (already selected)...Click Next
Click the [Layout] button
ROW: Drag the Name field here
(Dbl-click it and set subtotals to None)

DATA: Drag the Name field here
(If it doesn't change to Count of Name: Dbl-click it and set COUNT)

Click [OK]
Select a destination then click [Finish

That will create the kind of list you're looking for.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rishi Aggarwal" wrote:

I need to create a report showing the total list of people and the occurances.

Currently my list looks like the following:

A1
ADAM BROWN
ADAM BROWN
JOHN SMITH

I need a report showing me the name and the total number of occurances e.g.

A1 B1
ADAM BROWN 2
JOHN SMITH 1

Please advise on how this could be done?

count duplicates/total number of occurances
 

Thanks for the feedback....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rishi Aggarwal" wrote:

This is awesome !! Thanks.

"Ron Coderre" wrote:

I think this is the easiest way:

Select your list
DataPivot Table
Source: Excel List...Click Next
Range: (already selected)...Click Next
Click the [Layout] button
ROW: Drag the Name field here
(Dbl-click it and set subtotals to None)

DATA: Drag the Name field here
(If it doesn't change to Count of Name: Dbl-click it and set COUNT)

Click [OK]
Select a destination then click [Finish

That will create the kind of list you're looking for.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Rishi Aggarwal" wrote:

I need to create a report showing the total list of people and the occurances.

Currently my list looks like the following:

A1
ADAM BROWN
ADAM BROWN
JOHN SMITH

I need a report showing me the name and the total number of occurances e.g.

A1 B1
ADAM BROWN 2
JOHN SMITH 1

Please advise on how this could be done?