duplicates - counting AND displaying

[excel_user_1966]

I need to search my worksheet called Dates, and in the range of F6 to F100 find certain entries (but only look at the cells with data). The entries to copy onto my sheet Duplicates that I'm searching for will be kind of unique entries.. i.e.

If my lst is...

18th June
19th June
7th May
19th June
20th May
7th May

I wish that onto my sheet Duplicates the list will read:

18th June
7th May
19th June
20th May

there are only four different entries - because 7th May and 19th June are in it twice. So ignore a data if it already appears.

To the right of the list, there needs to be a number of how many times the date appears... i.e. now my list will show:

18th June | 1
7th May | 2
19th June | 2
20th May | 1


Can anyone guide me on how to do this?

 

[eliW]

Hi lying_happy_eyes,

All you have to do is to create a simple pivote table:
Put the dates in Row and Data (count), and the work is done.

Eli
This message was edited by eliW on 2002-04-19 06:43

 

[Mark W.]

On 2002-04-19 06:40, eliW wrote:

Hi lying_happy_eyes,

All you have to do is to create a simple pivote table:
Put the dates in Row and Data (count), and the work is done.

Eli
This message was edited by eliW on 2002-04-19 06:43

To "piggyback" on eliW's suggestion...

Initially, this PivotTable will be created in the same sequence as they're listed in your data set....

{"18th June ",1
;"19th June ",2
;"20th May ",1
;"7th May ",2}

By double clicking the ROW area field button for your dates, pressing the [ Advanced... ] button, and setting the AutoSort options to Descending using the "Count of..." (DATA area) field you can shuffle the duplicates to the top of the PivotTable list as follows...

{"7th May ",2
;"19th June ",2
;"18th June ",1
;"20th May ",1}