Count No. of times Dates are repeated

[Mandeep Dhami]

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami

 

[Ron Coderre]

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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

 

[Mandeep Dhami]

Thanks Ron.
I tried as instructed but sorry to say it is not working as I wanted.
The result is that, I am getting total number of times the dates are entered
against each names, where as I want that each new date should be counted only
once, i.e., if a person "A" is present for 10 days in a month, I should get
count 10 against his name no matter how many times he may have entered same
date against his name.
Hope I am able to convey what I require.

Cheers,
Mandeep Dhami

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami

 

[Ron Coderre]

Are you looking for how many times a name appears in the list? If yes, just
drag Count of Dates out of the DATA area and replace it with Count of Names.

Does that help?

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

Thanks Ron.
I tried as instructed but sorry to say it is not working as I wanted.
The result is that, I am getting total number of times the dates are entered
against each names, where as I want that each new date should be counted only
once, i.e., if a person "A" is present for 10 days in a month, I should get
count 10 against his name no matter how many times he may have entered same
date against his name.
Hope I am able to convey what I require.

Cheers,
Mandeep Dhami

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami

 

[Mandeep Dhami]

Ron,

I am looking at how many times the date is repeated against each name.
For Example:
Date Name
1-Dec-05 A
1-Dec-05 A
1-Dec-05 B
2-Dec-05 A
2-Dec-05 B
2-Dec-05 B
2-Dec-05 B
3-Dec-05 A
3-Dec-05 A
4-Dec-05 A

So here on 1-Dec-05 "A" appears twice and "B" once, similarly on 2-Dec-05
"A" appears once and "B" 3 times.

This should show as:
Date Name Frequency
1-Dec-05 A 2
B 1
2-Dec-05 A 1
B 3
Cheers,
Mandeep

Are you looking for how many times a name appears in the list? If yes, just
drag Count of Dates out of the DATA area and replace it with Count of Names.

Does that help?

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

Thanks Ron.
I tried as instructed but sorry to say it is not working as I wanted.
The result is that, I am getting total number of times the dates are entered
against each names, where as I want that each new date should be counted only
once, i.e., if a person "A" is present for 10 days in a month, I should get
count 10 against his name no matter how many times he may have entered same
date against his name.
Hope I am able to convey what I require.

Cheers,
Mandeep Dhami

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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


:

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami

 

[Ron Coderre]

OK...I think I (finally) understand. Try this with your Pivot Table:
First clear off all fields...then:
ROW:
Drag Date field first
Dbl-Click and set Subtotals to None

Drag Names Field second
Dbl-Click and set Subtotals to None

DATA: Count of Date

Then....once you see the Pivot Table right click on it and:
uncheck Grand totals for Rows
uncheck Grand totals for Columns
Click [OK]

Did I get it right this time?

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

Ron,

I am looking at how many times the date is repeated against each name.
For Example:
Date Name
1-Dec-05 A
1-Dec-05 A
1-Dec-05 B
2-Dec-05 A
2-Dec-05 B
2-Dec-05 B
2-Dec-05 B
3-Dec-05 A
3-Dec-05 A
4-Dec-05 A

So here on 1-Dec-05 "A" appears twice and "B" once, similarly on 2-Dec-05
"A" appears once and "B" 3 times.

This should show as:
Date Name Frequency
1-Dec-05 A 2
B 1
2-Dec-05 A 1
B 3
Cheers,
Mandeep

Are you looking for how many times a name appears in the list? If yes, just
drag Count of Dates out of the DATA area and replace it with Count of Names.

Does that help?

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

Thanks Ron.
I tried as instructed but sorry to say it is not working as I wanted.
The result is that, I am getting total number of times the dates are entered
against each names, where as I want that each new date should be counted only
once, i.e., if a person "A" is present for 10 days in a month, I should get
count 10 against his name no matter how many times he may have entered same
date against his name.
Hope I am able to convey what I require.

Cheers,
Mandeep Dhami


:

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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


:

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami

 

[Mandeep Dhami]

Thanks Ron,
Yes you have got it right this time.

OK...I think I (finally) understand. Try this with your Pivot Table:
First clear off all fields...then:
ROW:
Drag Date field first
Dbl-Click and set Subtotals to None

Drag Names Field second
Dbl-Click and set Subtotals to None

DATA: Count of Date

Then....once you see the Pivot Table right click on it and:
uncheck Grand totals for Rows
uncheck Grand totals for Columns
Click [OK]

Did I get it right this time?

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

Ron,

I am looking at how many times the date is repeated against each name.
For Example:
Date Name
1-Dec-05 A
1-Dec-05 A
1-Dec-05 B
2-Dec-05 A
2-Dec-05 B
2-Dec-05 B
2-Dec-05 B
3-Dec-05 A
3-Dec-05 A
4-Dec-05 A

So here on 1-Dec-05 "A" appears twice and "B" once, similarly on 2-Dec-05
"A" appears once and "B" 3 times.

This should show as:
Date Name Frequency
1-Dec-05 A 2
B 1
2-Dec-05 A 1
B 3
Cheers,
Mandeep

Are you looking for how many times a name appears in the list? If yes, just
drag Count of Dates out of the DATA area and replace it with Count of Names.

Does that help?

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


:

Thanks Ron.
I tried as instructed but sorry to say it is not working as I wanted.
The result is that, I am getting total number of times the dates are entered
against each names, where as I want that each new date should be counted only
once, i.e., if a person "A" is present for 10 days in a month, I should get
count 10 against his name no matter how many times he may have entered same
date against his name.
Hope I am able to convey what I require.

Cheers,
Mandeep Dhami


:

Have you considered a Pivot Table?

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Names field here
DATA: Drag the Dates field here
If it doesn't list as Count of Dates...dbl-click it and set it to Count
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each name and the count of dates.

To refresh the Pivot Table, just right click it and select Refresh Data.

Something you can use?

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


:

Hi,

I have a database where in dates are repeated in column D and names in
column I.
I want a formula in a different worksheet of the same file wherein I get the
count of dates column along with the individual name.
The intention is to get number of days each individual is present. The same
dates could be repeated any number of times for the same individual.

Example for the month of Dec. 2005
Dates Names
20 AD
15 BC
08 GH........etc

Cheers,
Mandeep Dhami