How to delete duplicates and also count them
 

I'm in dire need of help

I have a column of 2500 entries that account for no more than 10 duplicates.

So I know there has to be at least 250 unique entries. I'd like to
account for every item but I don't want to have the duplicates listed.

So Column A might look like this:

a
a
a
a
a
a
b
b
c
c
c
c
d
d
d

I'd ultimately want col a to just have

a
b
c
d

and then col b have how many times that instance showed up

a - 6
b - 2

etc
etc

How to delete duplicates and also count them
 

Eddie,

Select your range of data including the header record then

Data|Filter|Advanced filter

Select copy to another location
Check Unique records only
Select a range to copy to which should be a single cell
OK and you get a list of unique items. I piu my list in C1 down

In C2 enter this formula
=COUNTIF($A$2:$A$16,C2)
Drag down to the length of the filtered list.

Mike

"Eddie" wrote:

I'm in dire need of help

I have a column of 2500 entries that account for no more than 10 duplicates.

So I know there has to be at least 250 unique entries. I'd like to
account for every item but I don't want to have the duplicates listed.

So Column A might look like this:

a
a
a
a
a
a
b
b
c
c
c
c
d
d
d

I'd ultimately want col a to just have

a
b
c
d

and then col b have how many times that instance showed up

a - 6
b - 2

etc
etc

How to delete duplicates and also count them
 

Mike,

Thanks so much for the reply.

I did the first part in that I got a list of the items without duplication.
I'd like to now count how many times those showed up on the original list.

I got sort of lost in the second part of your reply.

"Mike H" wrote:

Eddie,

Select your range of data including the header record then

Data|Filter|Advanced filter

Select copy to another location
Check Unique records only
Select a range to copy to which should be a single cell
OK and you get a list of unique items. I piu my list in C1 down

In C2 enter this formula
=COUNTIF($A$2:$A$16,C2)
Drag down to the length of the filtered list.

Mike

"Eddie" wrote:

I'm in dire need of help

I have a column of 2500 entries that account for no more than 10 duplicates.

So I know there has to be at least 250 unique entries. I'd like to
account for every item but I don't want to have the duplicates listed.

So Column A might look like this:

a
a
a
a
a
a
b
b
c
c
c
c
d
d
d

I'd ultimately want col a to just have

a
b
c
d

and then col b have how many times that instance showed up

a - 6
b - 2

etc
etc

How to delete duplicates and also count them
 

Hi,

You now have a filtered list of unique items starting in (say) C2 and your
original data are in column A starting in A2.

Put the formula I gave you in D2 and it will count the instances of the
value in C2. Drag the formula down as long as the list in column C

Mike

"Eddie" wrote:

Mike,

Thanks so much for the reply.

I did the first part in that I got a list of the items without duplication.
I'd like to now count how many times those showed up on the original list.

I got sort of lost in the second part of your reply.

"Mike H" wrote:

Eddie,

Select your range of data including the header record then

Data|Filter|Advanced filter

Select copy to another location
Check Unique records only
Select a range to copy to which should be a single cell
OK and you get a list of unique items. I piu my list in C1 down

In C2 enter this formula
=COUNTIF($A$2:$A$16,C2)
Drag down to the length of the filtered list.

Mike

"Eddie" wrote:

I'm in dire need of help

I have a column of 2500 entries that account for no more than 10 duplicates.

So I know there has to be at least 250 unique entries. I'd like to
account for every item but I don't want to have the duplicates listed.

So Column A might look like this:

a
a
a
a
a
a
b
b
c
c
c
c
d
d
d

I'd ultimately want col a to just have

a
b
c
d

and then col b have how many times that instance showed up

a - 6
b - 2

etc
etc

How to delete duplicates and also count them
 

That worked excellent.

Thanks again for your help.

"Mike H" wrote:

Hi,

You now have a filtered list of unique items starting in (say) C2 and your
original data are in column A starting in A2.

Put the formula I gave you in D2 and it will count the instances of the
value in C2. Drag the formula down as long as the list in column C

Mike

"Eddie" wrote:

Mike,

Thanks so much for the reply.

I did the first part in that I got a list of the items without duplication.
I'd like to now count how many times those showed up on the original list.

I got sort of lost in the second part of your reply.

"Mike H" wrote:

Eddie,

Select your range of data including the header record then

Data|Filter|Advanced filter

Select copy to another location
Check Unique records only
Select a range to copy to which should be a single cell
OK and you get a list of unique items. I piu my list in C1 down

In C2 enter this formula
=COUNTIF($A$2:$A$16,C2)
Drag down to the length of the filtered list.

Mike

"Eddie" wrote:

I'm in dire need of help

I have a column of 2500 entries that account for no more than 10 duplicates.

So I know there has to be at least 250 unique entries. I'd like to
account for every item but I don't want to have the duplicates listed.

So Column A might look like this:

a
a
a
a
a
a
b
b
c
c
c
c
d
d
d

I'd ultimately want col a to just have

a
b
c
d

and then col b have how many times that instance showed up

a - 6
b - 2

etc
etc