| Counting unique items based on date |
|
 |
Index ‹ Excel ‹ worksheet functions
|
- Previous
- 1
- worksheet functions >> Table array in VLOOKUP (EXcel 2003)Hi
I use the VLOOKUP formula: =VLOOKUP(C21;PALIER;2) in which PALIER is a table
array and it works. I want now to have the "PALIER" changed by getting it
from another cell: =VLOOKUP(C21;C22;2) => i wrote "PALIER" in cell C22....
and that does not work anymore.
Is it a format issue , a table array issue or a Vlookup issue ?
Thanks.
jerome
- 2
- Excel Programming >> How can I check if a pop-up menu exists...Hi All,
Help with another (probably easy for you all) question. I use the code
below to add a pop-up menu that runs a macro.
On Error Resume Next
With Application
.CommandBars("Cell").Controls("GICAP Formatter").Delete
Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=False)
End With
With cBut
.Caption = "GICAP Formatter"
.Style = msoButtonCaption
.OnAction = "FormatIssuesWorkSheet"
End With
What I need to know is how to check if the pop-up menu exists the next
time the workbook is opened? The "on error" process doesn't work for me
in this case because I need this check in the middle of a subroutine.
However, I continue in the subroutine whether it exists or not.
Can someone help?
Thanks,
Chris
- 3
- Excel Programming >> How can I pass an array as TextToDisplay to a hyperlink?I have a procedure that searches through every sheet (108) in a
workbook for matching data then puts a hyperlink to those data onto a
"Hyperlog" worksheet.
here's part of the code
Option Explicit
Option Base 1
Dim hlText(3) as String
Dim rte as string
Dim num as string
Dim street as string
Dim r as Integer
Dim foundNum as Range
.
.
.
r = 3
hlTarget:=foundNum 'result of Find"
a do loop here as long as matches are being found
hlText(1) = rte: hlText(2) = num: hlText(3) = street
Sheets("HyperLog").Hyperlinks.Add Anchor:=Cells(r, 1), _
Address:="", SubAddress:=rte & "!" & hlTarget, TextToDisplay:=hlText()
If I use hlText() or hlText, I get invalid argument error.
If I use hlText(3) only the 3rd element is passed
hlText(1) & hlText(2) & hlText(3) works except when executing the
hyperlink
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)
CenterOnCell Range Target.SubAddress, Target.TextToDisplay
End Sub
fails at .TextToDisplay
What do I do now coach?
Jay
- 4
- worksheet functions >> Counting Dates - Not TextHi,
I'm trying to count the number of "date" occurrences on a particular month.
However in the same column, aside from dates, there are TEXT entries and
blank cells.
When I use the formula below it gives me a #VALUE!
Please edit the formula below so I can count the number of date occurences.
=SUMPRODUCT(--(MONTH('Date'!J2:J1116)=1))
Thank you.
- 5
- Excel Misc >> Changing upper case characters to upper/lowerI have a file of names and addresses that were entered in all upper case. Is
> there a fast way to change this file into upper and lower case? Thanks for
> any help you can give me. I have read some of the former answers regarding this and they refer to use of macro's....I a novice user and need more guidance
- 6
- Excel Programming >> I want a non-blank cell to return 25% in an adjacent cell.Spreadsheet keeps track of steps to be completed in a phase of a project. As
each step is complete I want the system to return the % that step worth (1
out of 4 = 25%). I will total each % of each step complete to give me a
total of the phase completed. How do I create a formula for each step of the
phase that will return the %? I thought this should be as simple as saying:
IF C5 is not blank, return 25% (in cell D5). Please help.
Thank-you.
- 7
- Excel Programming >> enable spell checkI created a form using excel and protected it so user's
can only input data were needed, but because i protected
the sheet, spell check doesnt work.
how can i enable spell check on a protected excel sheet?
- 8
- worksheet functions >> Linear regression errorsHi,
I have a set of data. When I graph the data on a scatter
plot and add a linear trendline, I also get an equation of
the line along with R2. However, when I use the worksheet
functions CORREL SLOPE and INTERCEPT I get different
values than the ones on the graph. Why? Please post back
Thanks,
Mike S.
- 9
- 10
- Excel Programming >> Help with Check Box and VBAHi,
I am new to excel and I am not sure if what I want is doable or not,
would really appreciate your help;
I want to create a two columns sheet in the first column I want a chec
box in the next column a date field. When the user checks the check bo
the current date and time would be put in the date column this way
can tell when that task was done. If the check mark is unchecked th
second cell needs to be cleared out if a date was there already.
If you can help me with both how to create the check box and how t
attach the code to it to do the above it will be great.
Thanks
--
Message posted from http://www.ExcelForum.com
- 11
- worksheet functions >> with weekly score sheet how do I column a weekly progressive aver.I am doing a Darts score sheet in excel 2003. I have "game scores", "game
shots", "game average", "games played", "(sum)total scores", "(sum)total
shots", "(sum)total average", improvement increase\decrease percentage, and I
have even done a "bar chart". What I can't work out is how to formulate a
column for "progressing average".
The only way I can get "progressing average" at the moment is by formulating
ONE cell as "(sum)total score"/"(sum)total shots" then entering the result
manualy into the corresponding cell so that the players can see how they are
averaging (up or down).
Any solutions please? don't make it too hard and complicated, I'm only "self
taught"
over about 5yrs and I'm still finding out lots of things.
- 12
- worksheet functions >> Help - my toolbars have disappearedI am looking for some help again. In excel, I opened a file which once
closed seems to have distorted the toolbars on excel. When I look in
tools customise, "standard" and "formatting" seem to have disappeared.
Does anyone know how I can get them back ? (If I try to create new
ones, it says that they already exist, even though I cannot find
them).
Thanks for your help.
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
- 13
- 14
- Excel Misc >> How do I add a theme in Excel?I am doing a project for school and they want me to convert a file to HTML
and then add a theme, but I can't find how to add one. Do I need to add the
theme before converting it to HTML, or is it something totally different?
- 15
- Excel Programming >> Organizing functionsGood afternoon everyone,
I've got more of a style question than technical one this time around. I'm
currently assembling all my custom functions and subs into one project. I
have been collecting some over the last couple of projects I've done, so I
thought it might be a good idea to have the ones I use very often together.
I am currently on 50-60 of them. The thing is that I've read so many
different opinions about how to store them.
Should I group them in different modules, or should I just create one big
module and comment every sub I have? Is there a processing-time difference
of having let's say 10 different modules or just having one big one?
Second question: should I save it as an add-in or would you recommend me
saving it as a normal spreadsheet?
I'd appreciate any comment on this matter.
Thanks guys ;o)
kj
|
| Author |
Message |
jcane
|
 Posted: Fri Jul 20 09:21:40 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
I have a sheet, with each row containing one record. Column A contains a
date, and column B contains a department. The contents of the column A
(date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
"dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
A + Column B.
I needed (ideally) a worksheet function that would allow me to obtain the
following information:
Based on a START DATE, END DATE and DEPT NAME return to me the number of
times I have the DEPT NAME occuring in my data for between the START DATE and
END DATE (both dates included) but counting multiple rows for the same date
as only 1 record. Thus if I have 27 rows for the same department with date
July 15, then it should give me only the value 1.
The time-part in the timestamp format can be ignored. It exists because of
input data coming from various sources and some sources store time and some
do not store time.
How could I do the above with a worksheet function?
Many thanks.
Excel534
|
| |
|
| |
|
Don
|
| Posted: Fri Jul 20 09:21:40 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
try this approach
=sumproduct((a4:a22>b1)*(a4:a22<b2)*(b4:b22="deptname"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
EMail@HideDomain.com
"DKS" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>I have a sheet, with each row containing one record. Column A contains a
> date, and column B contains a department. The contents of the column A
> (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B,
> Column
> A + Column B.
>
> I needed (ideally) a worksheet function that would allow me to obtain the
> following information:
>
> Based on a START DATE, END DATE and DEPT NAME return to me the number of
> times I have the DEPT NAME occuring in my data for between the START DATE
> and
> END DATE (both dates included) but counting multiple rows for the same
> date
> as only 1 record. Thus if I have 27 rows for the same department with
> date
> July 15, then it should give me only the value 1.
>
> The time-part in the timestamp format can be ignored. It exists because
> of
> input data coming from various sources and some sources store time and
> some
> do not store time.
>
> How could I do the above with a worksheet function?
>
> Many thanks.
|
| |
|
| |
|
RonCoderre
|
| Posted: Fri Jul 20 09:32:01 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Try something like this:
With this structure in A1:A16
Date Dept
1/1/2007 A
1/1/2007 A
1/1/2007 A
1/1/2007 B
1/1/2007 C
1/1/2007 C
1/1/2007 C
1/15/2007 A
1/15/2007 B
1/15/2007 C
1/15/2007 C
2/1/2007 A
2/1/2007 A
2/1/2007 B
2/1/2007 B
And...
E1: (StartDate eg: 01/01/2007)
F1: (EndDate eg: 01/31/2007)
G1: (DeptName eg: A)
Then this formula returns the number of unique dates in that range where the
DeptName matches the name in G1:
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
Using the above example, the formula returns: 2
Dept A appears at least once with 1/1/2007 and 1/15/2007
Is that something you can work with?
***********
Regards,
Ron
XL2003, WinXP
"DKS" wrote:
> I have a sheet, with each row containing one record. Column A contains a
> date, and column B contains a department. The contents of the column A
> (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> A + Column B.
>
> I needed (ideally) a worksheet function that would allow me to obtain the
> following information:
>
> Based on a START DATE, END DATE and DEPT NAME return to me the number of
> times I have the DEPT NAME occuring in my data for between the START DATE and
> END DATE (both dates included) but counting multiple rows for the same date
> as only 1 record. Thus if I have 27 rows for the same department with date
> July 15, then it should give me only the value 1.
>
> The time-part in the timestamp format can be ignored. It exists because of
> input data coming from various sources and some sources store time and some
> do not store time.
>
> How could I do the above with a worksheet function?
>
> Many thanks.
|
| |
|
| |
|
DKS
|
| Posted: Fri Jul 20 13:20:02 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Hi Don, Ron
Thanks for your suggestions. I tried them but they did not work. I forgot
to mention that the data may not always be sorted by date. I hope that does
not affect your logic?
To give you extra info on what went wrong: the formula of Don gave me an
extremely high number. For example: I had data for one calendar year but the
result of Don's formula was more than 366. This is not possible because
there are only 366 unique dates in a year. The formula of Ron returned zero
as a result.
Thanks.
"Ron Coderre" wrote:
> Try something like this:
>
> With this structure in A1:A16
> Date Dept
> 1/1/2007 A
> 1/1/2007 A
> 1/1/2007 A
> 1/1/2007 B
> 1/1/2007 C
> 1/1/2007 C
> 1/1/2007 C
> 1/15/2007 A
> 1/15/2007 B
> 1/15/2007 C
> 1/15/2007 C
> 2/1/2007 A
> 2/1/2007 A
> 2/1/2007 B
> 2/1/2007 B
>
> And...
> E1: (StartDate eg: 01/01/2007)
> F1: (EndDate eg: 01/31/2007)
> G1: (DeptName eg: A)
>
> Then this formula returns the number of unique dates in that range where the
> DeptName matches the name in G1:
> =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
>
> Using the above example, the formula returns: 2
> Dept A appears at least once with 1/1/2007 and 1/15/2007
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "DKS" wrote:
>
> > I have a sheet, with each row containing one record. Column A contains a
> > date, and column B contains a department. The contents of the column A
> > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > A + Column B.
> >
> > I needed (ideally) a worksheet function that would allow me to obtain the
> > following information:
> >
> > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > times I have the DEPT NAME occuring in my data for between the START DATE and
> > END DATE (both dates included) but counting multiple rows for the same date
> > as only 1 record. Thus if I have 27 rows for the same department with date
> > July 15, then it should give me only the value 1.
> >
> > The time-part in the timestamp format can be ignored. It exists because of
> > input data coming from various sources and some sources store time and some
> > do not store time.
> >
> > How could I do the above with a worksheet function?
> >
> > Many thanks.
|
| |
|
| |
|
RonCoderre
|
| Posted: Fri Jul 20 13:38:01 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
I tried scrambling the dates and DeptNames and I still get correct (to me)
results.
Try this:
Play with my posted example and see if it gives correct results under those
conditions.
-If yes
...Try putting some of your data into the example and test again.
-If No, then the data is the problem and you'll need to post some sample
data so we can see what you're dealing with?
Either way, please update us on your progress.
***********
Regards,
Ron
XL2003, WinXP
"DKS" wrote:
> Hi Don, Ron
>
> Thanks for your suggestions. I tried them but they did not work. I forgot
> to mention that the data may not always be sorted by date. I hope that does
> not affect your logic?
>
> To give you extra info on what went wrong: the formula of Don gave me an
> extremely high number. For example: I had data for one calendar year but the
> result of Don's formula was more than 366. This is not possible because
> there are only 366 unique dates in a year. The formula of Ron returned zero
> as a result.
>
> Thanks.
>
> "Ron Coderre" wrote:
>
> > Try something like this:
> >
> > With this structure in A1:A16
> > Date Dept
> > 1/1/2007 A
> > 1/1/2007 A
> > 1/1/2007 A
> > 1/1/2007 B
> > 1/1/2007 C
> > 1/1/2007 C
> > 1/1/2007 C
> > 1/15/2007 A
> > 1/15/2007 B
> > 1/15/2007 C
> > 1/15/2007 C
> > 2/1/2007 A
> > 2/1/2007 A
> > 2/1/2007 B
> > 2/1/2007 B
> >
> > And...
> > E1: (StartDate eg: 01/01/2007)
> > F1: (EndDate eg: 01/31/2007)
> > G1: (DeptName eg: A)
> >
> > Then this formula returns the number of unique dates in that range where the
> > DeptName matches the name in G1:
> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> >
> > Using the above example, the formula returns: 2
> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2003, WinXP
> >
> >
> > "DKS" wrote:
> >
> > > I have a sheet, with each row containing one record. Column A contains a
> > > date, and column B contains a department. The contents of the column A
> > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > > A + Column B.
> > >
> > > I needed (ideally) a worksheet function that would allow me to obtain the
> > > following information:
> > >
> > > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > > times I have the DEPT NAME occuring in my data for between the START DATE and
> > > END DATE (both dates included) but counting multiple rows for the same date
> > > as only 1 record. Thus if I have 27 rows for the same department with date
> > > July 15, then it should give me only the value 1.
> > >
> > > The time-part in the timestamp format can be ignored. It exists because of
> > > input data coming from various sources and some sources store time and some
> > > do not store time.
> > >
> > > How could I do the above with a worksheet function?
> > >
> > > Many thanks.
|
| |
|
| |
|
Don
|
| Posted: Fri Jul 20 13:51:04 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
I didn't see that you only wanted UNIQUE dates.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
EMail@HideDomain.com
"Ron Coderre" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>I tried scrambling the dates and DeptNames and I still get correct (to me)
> results.
>
> Try this:
> Play with my posted example and see if it gives correct results under
> those
> conditions.
> -If yes
> ...Try putting some of your data into the example and test again.
> -If No, then the data is the problem and you'll need to post some sample
> data so we can see what you're dealing with?
>
> Either way, please update us on your progress.
>
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "DKS" wrote:
>
>> Hi Don, Ron
>>
>> Thanks for your suggestions. I tried them but they did not work. I
>> forgot
>> to mention that the data may not always be sorted by date. I hope that
>> does
>> not affect your logic?
>>
>> To give you extra info on what went wrong: the formula of Don gave me an
>> extremely high number. For example: I had data for one calendar year but
>> the
>> result of Don's formula was more than 366. This is not possible because
>> there are only 366 unique dates in a year. The formula of Ron returned
>> zero
>> as a result.
>>
>> Thanks.
>>
>> "Ron Coderre" wrote:
>>
>> > Try something like this:
>> >
>> > With this structure in A1:A16
>> > Date Dept
>> > 1/1/2007 A
>> > 1/1/2007 A
>> > 1/1/2007 A
>> > 1/1/2007 B
>> > 1/1/2007 C
>> > 1/1/2007 C
>> > 1/1/2007 C
>> > 1/15/2007 A
>> > 1/15/2007 B
>> > 1/15/2007 C
>> > 1/15/2007 C
>> > 2/1/2007 A
>> > 2/1/2007 A
>> > 2/1/2007 B
>> > 2/1/2007 B
>> >
>> > And...
>> > E1: (StartDate eg: 01/01/2007)
>> > F1: (EndDate eg: 01/31/2007)
>> > G1: (DeptName eg: A)
>> >
>> > Then this formula returns the number of unique dates in that range
>> > where the
>> > DeptName matches the name in G1:
>> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
>> >
>> > Using the above example, the formula returns: 2
>> > Dept A appears at least once with 1/1/2007 and 1/15/2007
>> >
>> > Is that something you can work with?
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2003, WinXP
>> >
>> >
>> > "DKS" wrote:
>> >
>> > > I have a sheet, with each row containing one record. Column A
>> > > contains a
>> > > date, and column B contains a department. The contents of the column
>> > > A
>> > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp
>> > > e.g.
>> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B,
>> > > Column
>> > > A + Column B.
>> > >
>> > > I needed (ideally) a worksheet function that would allow me to obtain
>> > > the
>> > > following information:
>> > >
>> > > Based on a START DATE, END DATE and DEPT NAME return to me the number
>> > > of
>> > > times I have the DEPT NAME occuring in my data for between the START
>> > > DATE and
>> > > END DATE (both dates included) but counting multiple rows for the
>> > > same date
>> > > as only 1 record. Thus if I have 27 rows for the same department
>> > > with date
>> > > July 15, then it should give me only the value 1.
>> > >
>> > > The time-part in the timestamp format can be ignored. It exists
>> > > because of
>> > > input data coming from various sources and some sources store time
>> > > and some
>> > > do not store time.
>> > >
>> > > How could I do the above with a worksheet function?
>> > >
>> > > Many thanks.
|
| |
|
| |
|
RonCoderre
|
| Posted: Fri Jul 20 15:02:01 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Not me, Don (I don't have a preference)....but DKS wants unique dates. :)
(BTW......Congratulations on the MVP award)
***********
Regards,
Ron
XL2003, WinXP
"Don Guillett" wrote:
> I didn't see that you only wanted UNIQUE dates.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> EMail@HideDomain.com
> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
> >I tried scrambling the dates and DeptNames and I still get correct (to me)
> > results.
> >
> > Try this:
> > Play with my posted example and see if it gives correct results under
> > those
> > conditions.
> > -If yes
> > ...Try putting some of your data into the example and test again.
> > -If No, then the data is the problem and you'll need to post some sample
> > data so we can see what you're dealing with?
> >
> > Either way, please update us on your progress.
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2003, WinXP
> >
> >
> > "DKS" wrote:
> >
> >> Hi Don, Ron
> >>
> >> Thanks for your suggestions. I tried them but they did not work. I
> >> forgot
> >> to mention that the data may not always be sorted by date. I hope that
> >> does
> >> not affect your logic?
> >>
> >> To give you extra info on what went wrong: the formula of Don gave me an
> >> extremely high number. For example: I had data for one calendar year but
> >> the
> >> result of Don's formula was more than 366. This is not possible because
> >> there are only 366 unique dates in a year. The formula of Ron returned
> >> zero
> >> as a result.
> >>
> >> Thanks.
> >>
> >> "Ron Coderre" wrote:
> >>
> >> > Try something like this:
> >> >
> >> > With this structure in A1:A16
> >> > Date Dept
> >> > 1/1/2007 A
> >> > 1/1/2007 A
> >> > 1/1/2007 A
> >> > 1/1/2007 B
> >> > 1/1/2007 C
> >> > 1/1/2007 C
> >> > 1/1/2007 C
> >> > 1/15/2007 A
> >> > 1/15/2007 B
> >> > 1/15/2007 C
> >> > 1/15/2007 C
> >> > 2/1/2007 A
> >> > 2/1/2007 A
> >> > 2/1/2007 B
> >> > 2/1/2007 B
> >> >
> >> > And...
> >> > E1: (StartDate eg: 01/01/2007)
> >> > F1: (EndDate eg: 01/31/2007)
> >> > G1: (DeptName eg: A)
> >> >
> >> > Then this formula returns the number of unique dates in that range
> >> > where the
> >> > DeptName matches the name in G1:
> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> >> >
> >> > Using the above example, the formula returns: 2
> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> >> >
> >> > Is that something you can work with?
> >> > ***********
> >> > Regards,
> >> > Ron
> >> >
> >> > XL2003, WinXP
> >> >
> >> >
> >> > "DKS" wrote:
> >> >
> >> > > I have a sheet, with each row containing one record. Column A
> >> > > contains a
> >> > > date, and column B contains a department. The contents of the column
> >> > > A
> >> > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp
> >> > > e.g.
> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B,
> >> > > Column
> >> > > A + Column B.
> >> > >
> >> > > I needed (ideally) a worksheet function that would allow me to obtain
> >> > > the
> >> > > following information:
> >> > >
> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the number
> >> > > of
> >> > > times I have the DEPT NAME occuring in my data for between the START
> >> > > DATE and
> >> > > END DATE (both dates included) but counting multiple rows for the
> >> > > same date
> >> > > as only 1 record. Thus if I have 27 rows for the same department
> >> > > with date
> >> > > July 15, then it should give me only the value 1.
> >> > >
> >> > > The time-part in the timestamp format can be ignored. It exists
> >> > > because of
> >> > > input data coming from various sources and some sources store time
> >> > > and some
> >> > > do not store time.
> >> > >
> >> > > How could I do the above with a worksheet function?
> >> > >
> >> > > Many thanks.
>
>
|
| |
|
| |
|
Don
|
| Posted: Fri Jul 20 17:59:09 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
I was saying that yours was the way to go and "Gracias Senor" for the kind
words.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
EMail@HideDomain.com
"Ron Coderre" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> Not me, Don (I don't have a preference)....but DKS wants unique dates. :)
>
> (BTW......Congratulations on the MVP award)
>
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "Don Guillett" wrote:
>
>> I didn't see that you only wanted UNIQUE dates.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> EMail@HideDomain.com
>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
>> news:EMail@HideDomain.com...
>> >I tried scrambling the dates and DeptNames and I still get correct (to
>> >me)
>> > results.
>> >
>> > Try this:
>> > Play with my posted example and see if it gives correct results under
>> > those
>> > conditions.
>> > -If yes
>> > ...Try putting some of your data into the example and test again.
>> > -If No, then the data is the problem and you'll need to post some
>> > sample
>> > data so we can see what you're dealing with?
>> >
>> > Either way, please update us on your progress.
>> >
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2003, WinXP
>> >
>> >
>> > "DKS" wrote:
>> >
>> >> Hi Don, Ron
>> >>
>> >> Thanks for your suggestions. I tried them but they did not work. I
>> >> forgot
>> >> to mention that the data may not always be sorted by date. I hope
>> >> that
>> >> does
>> >> not affect your logic?
>> >>
>> >> To give you extra info on what went wrong: the formula of Don gave me
>> >> an
>> >> extremely high number. For example: I had data for one calendar year
>> >> but
>> >> the
>> >> result of Don's formula was more than 366. This is not possible
>> >> because
>> >> there are only 366 unique dates in a year. The formula of Ron
>> >> returned
>> >> zero
>> >> as a result.
>> >>
>> >> Thanks.
>> >>
>> >> "Ron Coderre" wrote:
>> >>
>> >> > Try something like this:
>> >> >
>> >> > With this structure in A1:A16
>> >> > Date Dept
>> >> > 1/1/2007 A
>> >> > 1/1/2007 A
>> >> > 1/1/2007 A
>> >> > 1/1/2007 B
>> >> > 1/1/2007 C
>> >> > 1/1/2007 C
>> >> > 1/1/2007 C
>> >> > 1/15/2007 A
>> >> > 1/15/2007 B
>> >> > 1/15/2007 C
>> >> > 1/15/2007 C
>> >> > 2/1/2007 A
>> >> > 2/1/2007 A
>> >> > 2/1/2007 B
>> >> > 2/1/2007 B
>> >> >
>> >> > And...
>> >> > E1: (StartDate eg: 01/01/2007)
>> >> > F1: (EndDate eg: 01/31/2007)
>> >> > G1: (DeptName eg: A)
>> >> >
>> >> > Then this formula returns the number of unique dates in that range
>> >> > where the
>> >> > DeptName matches the name in G1:
>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
>> >> >
>> >> > Using the above example, the formula returns: 2
>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
>> >> >
>> >> > Is that something you can work with?
>> >> > ***********
>> >> > Regards,
>> >> > Ron
>> >> >
>> >> > XL2003, WinXP
>> >> >
>> >> >
>> >> > "DKS" wrote:
>> >> >
>> >> > > I have a sheet, with each row containing one record. Column A
>> >> > > contains a
>> >> > > date, and column B contains a department. The contents of the
>> >> > > column
>> >> > > A
>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp
>> >> > > e.g.
>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column
>> >> > > B,
>> >> > > Column
>> >> > > A + Column B.
>> >> > >
>> >> > > I needed (ideally) a worksheet function that would allow me to
>> >> > > obtain
>> >> > > the
>> >> > > following information:
>> >> > >
>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
>> >> > > number
>> >> > > of
>> >> > > times I have the DEPT NAME occuring in my data for between the
>> >> > > START
>> >> > > DATE and
>> >> > > END DATE (both dates included) but counting multiple rows for the
>> >> > > same date
>> >> > > as only 1 record. Thus if I have 27 rows for the same department
>> >> > > with date
>> >> > > July 15, then it should give me only the value 1.
>> >> > >
>> >> > > The time-part in the timestamp format can be ignored. It exists
>> >> > > because of
>> >> > > input data coming from various sources and some sources store time
>> >> > > and some
>> >> > > do not store time.
>> >> > >
>> >> > > How could I do the above with a worksheet function?
>> >> > >
>> >> > > Many thanks.
>>
>>
|
| |
|
| |
|
T
|
| Posted: Fri Jul 20 22:34:14 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Congrats, Don!
I has assumed you were already but didn't make it public.
--
Biff
Microsoft Excel MVP
"Don Guillett" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>I was saying that yours was the way to go and "Gracias Senor" for the kind
>words.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> EMail@HideDomain.com
> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
>> :)
>>
>> (BTW......Congratulations on the MVP award)
>>
>> ***********
>> Regards,
>> Ron
>>
>> XL2003, WinXP
>>
>>
>> "Don Guillett" wrote:
>>
>>> I didn't see that you only wanted UNIQUE dates.
>>>
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> EMail@HideDomain.com
>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
>>> news:EMail@HideDomain.com...
>>> >I tried scrambling the dates and DeptNames and I still get correct (to
>>> >me)
>>> > results.
>>> >
>>> > Try this:
>>> > Play with my posted example and see if it gives correct results under
>>> > those
>>> > conditions.
>>> > -If yes
>>> > ...Try putting some of your data into the example and test again.
>>> > -If No, then the data is the problem and you'll need to post some
>>> > sample
>>> > data so we can see what you're dealing with?
>>> >
>>> > Either way, please update us on your progress.
>>> >
>>> > ***********
>>> > Regards,
>>> > Ron
>>> >
>>> > XL2003, WinXP
>>> >
>>> >
>>> > "DKS" wrote:
>>> >
>>> >> Hi Don, Ron
>>> >>
>>> >> Thanks for your suggestions. I tried them but they did not work. I
>>> >> forgot
>>> >> to mention that the data may not always be sorted by date. I hope
>>> >> that
>>> >> does
>>> >> not affect your logic?
>>> >>
>>> >> To give you extra info on what went wrong: the formula of Don gave me
>>> >> an
>>> >> extremely high number. For example: I had data for one calendar year
>>> >> but
>>> >> the
>>> >> result of Don's formula was more than 366. This is not possible
>>> >> because
>>> >> there are only 366 unique dates in a year. The formula of Ron
>>> >> returned
>>> >> zero
>>> >> as a result.
>>> >>
>>> >> Thanks.
>>> >>
>>> >> "Ron Coderre" wrote:
>>> >>
>>> >> > Try something like this:
>>> >> >
>>> >> > With this structure in A1:A16
>>> >> > Date Dept
>>> >> > 1/1/2007 A
>>> >> > 1/1/2007 A
>>> >> > 1/1/2007 A
>>> >> > 1/1/2007 B
>>> >> > 1/1/2007 C
>>> >> > 1/1/2007 C
>>> >> > 1/1/2007 C
>>> >> > 1/15/2007 A
>>> >> > 1/15/2007 B
>>> >> > 1/15/2007 C
>>> >> > 1/15/2007 C
>>> >> > 2/1/2007 A
>>> >> > 2/1/2007 A
>>> >> > 2/1/2007 B
>>> >> > 2/1/2007 B
>>> >> >
>>> >> > And...
>>> >> > E1: (StartDate eg: 01/01/2007)
>>> >> > F1: (EndDate eg: 01/31/2007)
>>> >> > G1: (DeptName eg: A)
>>> >> >
>>> >> > Then this formula returns the number of unique dates in that range
>>> >> > where the
>>> >> > DeptName matches the name in G1:
>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
>>> >> >
>>> >> > Using the above example, the formula returns: 2
>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
>>> >> >
>>> >> > Is that something you can work with?
>>> >> > ***********
>>> >> > Regards,
>>> >> > Ron
>>> >> >
>>> >> > XL2003, WinXP
>>> >> >
>>> >> >
>>> >> > "DKS" wrote:
>>> >> >
>>> >> > > I have a sheet, with each row containing one record. Column A
>>> >> > > contains a
>>> >> > > date, and column B contains a department. The contents of the
>>> >> > > column
>>> >> > > A
>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
>>> >> > > timestamp
>>> >> > > e.g.
>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
>>> >> > > Column B,
>>> >> > > Column
>>> >> > > A + Column B.
>>> >> > >
>>> >> > > I needed (ideally) a worksheet function that would allow me to
>>> >> > > obtain
>>> >> > > the
>>> >> > > following information:
>>> >> > >
>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
>>> >> > > number
>>> >> > > of
>>> >> > > times I have the DEPT NAME occuring in my data for between the
>>> >> > > START
>>> >> > > DATE and
>>> >> > > END DATE (both dates included) but counting multiple rows for the
>>> >> > > same date
>>> >> > > as only 1 record. Thus if I have 27 rows for the same department
>>> >> > > with date
>>> >> > > July 15, then it should give me only the value 1.
>>> >> > >
>>> >> > > The time-part in the timestamp format can be ignored. It exists
>>> >> > > because of
>>> >> > > input data coming from various sources and some sources store
>>> >> > > time
>>> >> > > and some
>>> >> > > do not store time.
>>> >> > >
>>> >> > > How could I do the above with a worksheet function?
>>> >> > >
>>> >> > > Many thanks.
>>>
>>>
>
|
| |
|
| |
|
Don
|
| Posted: Sat Jul 21 07:11:19 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Thanks again. To you too.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
EMail@HideDomain.com
"T. Valko" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
> Congrats, Don!
>
> I has assumed you were already but didn't make it public.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Don Guillett" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
>>I was saying that yours was the way to go and "Gracias Senor" for the kind
>>words.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> EMail@HideDomain.com
>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
>> news:EMail@HideDomain.com...
>>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
>>> :)
>>>
>>> (BTW......Congratulations on the MVP award)
>>>
>>> ***********
>>> Regards,
>>> Ron
>>>
>>> XL2003, WinXP
>>>
>>>
>>> "Don Guillett" wrote:
>>>
>>>> I didn't see that you only wanted UNIQUE dates.
>>>>
>>>> --
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> EMail@HideDomain.com
>>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
>>>> news:EMail@HideDomain.com...
>>>> >I tried scrambling the dates and DeptNames and I still get correct (to
>>>> >me)
>>>> > results.
>>>> >
>>>> > Try this:
>>>> > Play with my posted example and see if it gives correct results under
>>>> > those
>>>> > conditions.
>>>> > -If yes
>>>> > ...Try putting some of your data into the example and test again.
>>>> > -If No, then the data is the problem and you'll need to post some
>>>> > sample
>>>> > data so we can see what you're dealing with?
>>>> >
>>>> > Either way, please update us on your progress.
>>>> >
>>>> > ***********
>>>> > Regards,
>>>> > Ron
>>>> >
>>>> > XL2003, WinXP
>>>> >
>>>> >
>>>> > "DKS" wrote:
>>>> >
>>>> >> Hi Don, Ron
>>>> >>
>>>> >> Thanks for your suggestions. I tried them but they did not work. I
>>>> >> forgot
>>>> >> to mention that the data may not always be sorted by date. I hope
>>>> >> that
>>>> >> does
>>>> >> not affect your logic?
>>>> >>
>>>> >> To give you extra info on what went wrong: the formula of Don gave
>>>> >> me an
>>>> >> extremely high number. For example: I had data for one calendar
>>>> >> year but
>>>> >> the
>>>> >> result of Don's formula was more than 366. This is not possible
>>>> >> because
>>>> >> there are only 366 unique dates in a year. The formula of Ron
>>>> >> returned
>>>> >> zero
>>>> >> as a result.
>>>> >>
>>>> >> Thanks.
>>>> >>
>>>> >> "Ron Coderre" wrote:
>>>> >>
>>>> >> > Try something like this:
>>>> >> >
>>>> >> > With this structure in A1:A16
>>>> >> > Date Dept
>>>> >> > 1/1/2007 A
>>>> >> > 1/1/2007 A
>>>> >> > 1/1/2007 A
>>>> >> > 1/1/2007 B
>>>> >> > 1/1/2007 C
>>>> >> > 1/1/2007 C
>>>> >> > 1/1/2007 C
>>>> >> > 1/15/2007 A
>>>> >> > 1/15/2007 B
>>>> >> > 1/15/2007 C
>>>> >> > 1/15/2007 C
>>>> >> > 2/1/2007 A
>>>> >> > 2/1/2007 A
>>>> >> > 2/1/2007 B
>>>> >> > 2/1/2007 B
>>>> >> >
>>>> >> > And...
>>>> >> > E1: (StartDate eg: 01/01/2007)
>>>> >> > F1: (EndDate eg: 01/31/2007)
>>>> >> > G1: (DeptName eg: A)
>>>> >> >
>>>> >> > Then this formula returns the number of unique dates in that range
>>>> >> > where the
>>>> >> > DeptName matches the name in G1:
>>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
>>>> >> >
>>>> >> > Using the above example, the formula returns: 2
>>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
>>>> >> >
>>>> >> > Is that something you can work with?
>>>> >> > ***********
>>>> >> > Regards,
>>>> >> > Ron
>>>> >> >
>>>> >> > XL2003, WinXP
>>>> >> >
>>>> >> >
>>>> >> > "DKS" wrote:
>>>> >> >
>>>> >> > > I have a sheet, with each row containing one record. Column A
>>>> >> > > contains a
>>>> >> > > date, and column B contains a department. The contents of the
>>>> >> > > column
>>>> >> > > A
>>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
>>>> >> > > timestamp
>>>> >> > > e.g.
>>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
>>>> >> > > Column B,
>>>> >> > > Column
>>>> >> > > A + Column B.
>>>> >> > >
>>>> >> > > I needed (ideally) a worksheet function that would allow me to
>>>> >> > > obtain
>>>> >> > > the
>>>> >> > > following information:
>>>> >> > >
>>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
>>>> >> > > number
>>>> >> > > of
>>>> >> > > times I have the DEPT NAME occuring in my data for between the
>>>> >> > > START
>>>> >> > > DATE and
>>>> >> > > END DATE (both dates included) but counting multiple rows for
>>>> >> > > the
>>>> >> > > same date
>>>> >> > > as only 1 record. Thus if I have 27 rows for the same
>>>> >> > > department
>>>> >> > > with date
>>>> >> > > July 15, then it should give me only the value 1.
>>>> >> > >
>>>> >> > > The time-part in the timestamp format can be ignored. It exists
>>>> >> > > because of
>>>> >> > > input data coming from various sources and some sources store
>>>> >> > > time
>>>> >> > > and some
>>>> >> > > do not store time.
>>>> >> > >
>>>> >> > > How could I do the above with a worksheet function?
>>>> >> > >
>>>> >> > > Many thanks.
>>>>
>>>>
>>
>
>
|
| |
|
| |
|
Teethlessmama
|
| Posted: Sat Jul 21 13:10:02 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Try this:
=SUM(IF(FREQUENCY(IF((Dept="A")*(TEXT(Date,"mmmyy")="Jan07"),INT(Date)),INT(Date))>0,1))
"DKS" wrote:
> I have a sheet, with each row containing one record. Column A contains a
> date, and column B contains a department. The contents of the column A
> (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> A + Column B.
>
> I needed (ideally) a worksheet function that would allow me to obtain the
> following information:
>
> Based on a START DATE, END DATE and DEPT NAME return to me the number of
> times I have the DEPT NAME occuring in my data for between the START DATE and
> END DATE (both dates included) but counting multiple rows for the same date
> as only 1 record. Thus if I have 27 rows for the same department with date
> July 15, then it should give me only the value 1.
>
> The time-part in the timestamp format can be ignored. It exists because of
> input data coming from various sources and some sources store time and some
> do not store time.
>
> How could I do the above with a worksheet function?
>
> Many thanks.
|
| |
|
| |
|
DKS
|
| Posted: Sat Jul 21 14:52:00 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Ron,
Your code worked. I had made a mistake whilst typing the formula you gave.
Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000
to signify the full range. And apparently with that it did not work.
But now I have corrected the formula and it seems to be working like charm.
I still have to test with time-stamps but I believe it should work.
thanks for your help.
"Ron Coderre" wrote:
> I tried scrambling the dates and DeptNames and I still get correct (to me)
> results.
>
> Try this:
> Play with my posted example and see if it gives correct results under those
> conditions.
> -If yes
> ...Try putting some of your data into the example and test again.
> -If No, then the data is the problem and you'll need to post some sample
> data so we can see what you're dealing with?
>
> Either way, please update us on your progress.
>
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "DKS" wrote:
>
> > Hi Don, Ron
> >
> > Thanks for your suggestions. I tried them but they did not work. I forgot
> > to mention that the data may not always be sorted by date. I hope that does
> > not affect your logic?
> >
> > To give you extra info on what went wrong: the formula of Don gave me an
> > extremely high number. For example: I had data for one calendar year but the
> > result of Don's formula was more than 366. This is not possible because
> > there are only 366 unique dates in a year. The formula of Ron returned zero
> > as a result.
> >
> > Thanks.
> >
> > "Ron Coderre" wrote:
> >
> > > Try something like this:
> > >
> > > With this structure in A1:A16
> > > Date Dept
> > > 1/1/2007 A
> > > 1/1/2007 A
> > > 1/1/2007 A
> > > 1/1/2007 B
> > > 1/1/2007 C
> > > 1/1/2007 C
> > > 1/1/2007 C
> > > 1/15/2007 A
> > > 1/15/2007 B
> > > 1/15/2007 C
> > > 1/15/2007 C
> > > 2/1/2007 A
> > > 2/1/2007 A
> > > 2/1/2007 B
> > > 2/1/2007 B
> > >
> > > And...
> > > E1: (StartDate eg: 01/01/2007)
> > > F1: (EndDate eg: 01/31/2007)
> > > G1: (DeptName eg: A)
> > >
> > > Then this formula returns the number of unique dates in that range where the
> > > DeptName matches the name in G1:
> > > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > >
> > > Using the above example, the formula returns: 2
> > > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > >
> > > Is that something you can work with?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2003, WinXP
> > >
> > >
> > > "DKS" wrote:
> > >
> > > > I have a sheet, with each row containing one record. Column A contains a
> > > > date, and column B contains a department. The contents of the column A
> > > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > > > A + Column B.
> > > >
> > > > I needed (ideally) a worksheet function that would allow me to obtain the
> > > > following information:
> > > >
> > > > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > > > times I have the DEPT NAME occuring in my data for between the START DATE and
> > > > END DATE (both dates included) but counting multiple rows for the same date
> > > > as only 1 record. Thus if I have 27 rows for the same department with date
> > > > July 15, then it should give me only the value 1.
> > > >
> > > > The time-part in the timestamp format can be ignored. It exists because of
> > > > input data coming from various sources and some sources store time and some
> > > > do not store time.
> > > >
> > > > How could I do the above with a worksheet function?
> > > >
> > > > Many thanks.
|
| |
|
| |
|
DKS
|
| Posted: Sat Jul 21 15:02:00 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
First test with timestamp included in the date, and the date being one of the
extremes being tested and the formula does not work.
For example: in your test-data hereunder if I put only one record for dept A
for date March 15; and if I test for Jan 1st till Marc 15th for dept A, I get
one number less than the actual number.
Just FYI.
"DKS" wrote:
> Ron,
>
> Your code worked. I had made a mistake whilst typing the formula you gave.
> Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000
> to signify the full range. And apparently with that it did not work.
>
> But now I have corrected the formula and it seems to be working like charm.
> I still have to test with time-stamps but I believe it should work.
>
> thanks for your help.
>
> "Ron Coderre" wrote:
>
> > I tried scrambling the dates and DeptNames and I still get correct (to me)
> > results.
> >
> > Try this:
> > Play with my posted example and see if it gives correct results under those
> > conditions.
> > -If yes
> > ...Try putting some of your data into the example and test again.
> > -If No, then the data is the problem and you'll need to post some sample
> > data so we can see what you're dealing with?
> >
> > Either way, please update us on your progress.
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2003, WinXP
> >
> >
> > "DKS" wrote:
> >
> > > Hi Don, Ron
> > >
> > > Thanks for your suggestions. I tried them but they did not work. I forgot
> > > to mention that the data may not always be sorted by date. I hope that does
> > > not affect your logic?
> > >
> > > To give you extra info on what went wrong: the formula of Don gave me an
> > > extremely high number. For example: I had data for one calendar year but the
> > > result of Don's formula was more than 366. This is not possible because
> > > there are only 366 unique dates in a year. The formula of Ron returned zero
> > > as a result.
> > >
> > > Thanks.
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > Try something like this:
> > > >
> > > > With this structure in A1:A16
> > > > Date Dept
> > > > 1/1/2007 A
> > > > 1/1/2007 A
> > > > 1/1/2007 A
> > > > 1/1/2007 B
> > > > 1/1/2007 C
> > > > 1/1/2007 C
> > > > 1/1/2007 C
> > > > 1/15/2007 A
> > > > 1/15/2007 B
> > > > 1/15/2007 C
> > > > 1/15/2007 C
> > > > 2/1/2007 A
> > > > 2/1/2007 A
> > > > 2/1/2007 B
> > > > 2/1/2007 B
> > > >
> > > > And...
> > > > E1: (StartDate eg: 01/01/2007)
> > > > F1: (EndDate eg: 01/31/2007)
> > > > G1: (DeptName eg: A)
> > > >
> > > > Then this formula returns the number of unique dates in that range where the
> > > > DeptName matches the name in G1:
> > > > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > >
> > > > Using the above example, the formula returns: 2
> > > > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > >
> > > > Is that something you can work with?
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2003, WinXP
> > > >
> > > >
> > > > "DKS" wrote:
> > > >
> > > > > I have a sheet, with each row containing one record. Column A contains a
> > > > > date, and column B contains a department. The contents of the column A
> > > > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > > > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > > > > A + Column B.
> > > > >
> > > > > I needed (ideally) a worksheet function that would allow me to obtain the
> > > > > following information:
> > > > >
> > > > > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > > > > times I have the DEPT NAME occuring in my data for between the START DATE and
> > > > > END DATE (both dates included) but counting multiple rows for the same date
> > > > > as only 1 record. Thus if I have 27 rows for the same department with date
> > > > > July 15, then it should give me only the value 1.
> > > > >
> > > > > The time-part in the timestamp format can be ignored. It exists because of
> > > > > input data coming from various sources and some sources store time and some
> > > > > do not store time.
> > > > >
> > > > > How could I do the above with a worksheet function?
> > > > >
> > > > > Many thanks.
|
| |
|
| |
|
RonCoderre
|
| Posted: Sat Jul 21 21:08:00 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Wait a minute......timestamp?!?
You may not think so, but that was an important detail to leave out.
Here's why:
Excel treats dates as whole numbers and times as decimal fractions of a day.
15-MAR-2007 is 39,156 to Excel
Noon on 15-MAR-2007 is 39156.5
We've been matching dates. So, when you enter something like 03/15/2007
12:00:00, that value is greater than the tested end point of 03/15/2007.
To compensate for time values, try this formula:
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),INDEX(INT((B2:B20=G1)*(A2:A20>=E1)*(INT(A2:A20)<=F1)*A2:A20),0),0)))
Does that help?
***********
Regards,
Ron
XL2003, WinXP
"DKS" wrote:
> First test with timestamp included in the date, and the date being one of the
> extremes being tested and the formula does not work.
>
> For example: in your test-data hereunder if I put only one record for dept A
> for date March 15; and if I test for Jan 1st till Marc 15th for dept A, I get
> one number less than the actual number.
>
> Just FYI.
>
> "DKS" wrote:
>
> > Ron,
> >
> > Your code worked. I had made a mistake whilst typing the formula you gave.
> > Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000
> > to signify the full range. And apparently with that it did not work.
> >
> > But now I have corrected the formula and it seems to be working like charm.
> > I still have to test with time-stamps but I believe it should work.
> >
> > thanks for your help.
> >
> > "Ron Coderre" wrote:
> >
> > > I tried scrambling the dates and DeptNames and I still get correct (to me)
> > > results.
> > >
> > > Try this:
> > > Play with my posted example and see if it gives correct results under those
> > > conditions.
> > > -If yes
> > > ...Try putting some of your data into the example and test again.
> > > -If No, then the data is the problem and you'll need to post some sample
> > > data so we can see what you're dealing with?
> > >
> > > Either way, please update us on your progress.
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2003, WinXP
> > >
> > >
> > > "DKS" wrote:
> > >
> > > > Hi Don, Ron
> > > >
> > > > Thanks for your suggestions. I tried them but they did not work. I forgot
> > > > to mention that the data may not always be sorted by date. I hope that does
> > > > not affect your logic?
> > > >
> > > > To give you extra info on what went wrong: the formula of Don gave me an
> > > > extremely high number. For example: I had data for one calendar year but the
> > > > result of Don's formula was more than 366. This is not possible because
> > > > there are only 366 unique dates in a year. The formula of Ron returned zero
> > > > as a result.
> > > >
> > > > Thanks.
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > Try something like this:
> > > > >
> > > > > With this structure in A1:A16
> > > > > Date Dept
> > > > > 1/1/2007 A
> > > > > 1/1/2007 A
> > > > > 1/1/2007 A
> > > > > 1/1/2007 B
> > > > > 1/1/2007 C
> > > > > 1/1/2007 C
> > > > > 1/1/2007 C
> > > > > 1/15/2007 A
> > > > > 1/15/2007 B
> > > > > 1/15/2007 C
> > > > > 1/15/2007 C
> > > > > 2/1/2007 A
> > > > > 2/1/2007 A
> > > > > 2/1/2007 B
> > > > > 2/1/2007 B
> > > > >
> > > > > And...
> > > > > E1: (StartDate eg: 01/01/2007)
> > > > > F1: (EndDate eg: 01/31/2007)
> > > > > G1: (DeptName eg: A)
> > > > >
> > > > > Then this formula returns the number of unique dates in that range where the
> > > > > DeptName matches the name in G1:
> > > > > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > > >
> > > > > Using the above example, the formula returns: 2
> > > > > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > > >
> > > > > Is that something you can work with?
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2003, WinXP
> > > > >
> > > > >
> > > > > "DKS" wrote:
> > > > >
> > > > > > I have a sheet, with each row containing one record. Column A contains a
> > > > > > date, and column B contains a department. The contents of the column A
> > > > > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > > > > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > > > > > A + Column B.
> > > > > >
> > > > > > I needed (ideally) a worksheet function that would allow me to obtain the
> > > > > > following information:
> > > > > >
> > > > > > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > > > > > times I have the DEPT NAME occuring in my data for between the START DATE and
> > > > > > END DATE (both dates included) but counting multiple rows for the same date
> > > > > > as only 1 record. Thus if I have 27 rows for the same department with date
> > > > > > July 15, then it should give me only the value 1.
> > > > > >
> > > > > > The time-part in the timestamp format can be ignored. It exists because of
> > > > > > input data coming from various sources and some sources store time and some
> > > > > > do not store time.
> > > > > >
> > > > > > How could I do the above with a worksheet function?
> > > > > >
> > > > > > Many thanks.
|
| |
|
| |
|
RonCoderre
|
| Posted: Sun Jul 22 11:58:03 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
A somewhat shorter version implements the FREQUENCY function. My only
reluctance in using it is that it can be a bit confusing to figure out.
With the actual data in A2:B20, Col_A contains dates, Col_B contains
DeptNames,
and...
E1: (StartDate)
F1: (EndDate)
G1: (a dept name)
Here are both versions:
NON-array formula
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),INDEX(INT((B2:B20=G1)*(A2:A20>=E1)*(INT(A2:A20)<=F1)*A2:A20),0),0)))
ARRAY formula (committed with Ctrl+Shift+Enter, instead of Enter)
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),INT((B2:B20=G1)*(A2:A20>=E1)*(INT(A2:A20)<=F1)*A2:A20),0)))
NON-array formula, using FREQUENCY
=SUMPRODUCT(--((B2:B21=G1)*(FREQUENCY((B2:B20=G1)*(A2:A20>=E1)*(INT(A2:A20)<=F1)*INT(A2:A20),INT(A2:A20))>0)))
Note the first part: (B2:B21=G1) extends 1 row below the actual data area to
accommodate the way the FREQUENCY function constructs its bins.
Does that help?
***********
Regards,
Ron
XL2003, WinXP
"Ron Coderre" wrote:
> Wait a minute......timestamp?!?
> You may not think so, but that was an important detail to leave out.
>
> Here's why:
>
> Excel treats dates as whole numbers and times as decimal fractions of a day.
> 15-MAR-2007 is 39,156 to Excel
> Noon on 15-MAR-2007 is 39156.5
>
> We've been matching dates. So, when you enter something like 03/15/2007
> 12:00:00, that value is greater than the tested end point of 03/15/2007.
>
> To compensate for time values, try this formula:
> =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),INDEX(INT((B2:B20=G1)*(A2:A20>=E1)*(INT(A2:A20)<=F1)*A2:A20),0),0)))
>
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "DKS" wrote:
>
> > First test with timestamp included in the date, and the date being one of the
> > extremes being tested and the formula does not work.
> >
> > For example: in your test-data hereunder if I put only one record for dept A
> > for date March 15; and if I test for Jan 1st till Marc 15th for dept A, I get
> > one number less than the actual number.
> >
> > Just FYI.
> >
> > "DKS" wrote:
> >
> > > Ron,
> > >
> > > Your code worked. I had made a mistake whilst typing the formula you gave.
> > > Instead of typing A:A as you have mentioned, I inadvertently typed A2:A25000
> > > to signify the full range. And apparently with that it did not work.
> > >
> > > But now I have corrected the formula and it seems to be working like charm.
> > > I still have to test with time-stamps but I believe it should work.
> > >
> > > thanks for your help.
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > I tried scrambling the dates and DeptNames and I still get correct (to me)
> > > > results.
> > > >
> > > > Try this:
> > > > Play with my posted example and see if it gives correct results under those
> > > > conditions.
> > > > -If yes
> > > > ...Try putting some of your data into the example and test again.
> > > > -If No, then the data is the problem and you'll need to post some sample
> > > > data so we can see what you're dealing with?
> > > >
> > > > Either way, please update us on your progress.
> > > >
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2003, WinXP
> > > >
> > > >
> > > > "DKS" wrote:
> > > >
> > > > > Hi Don, Ron
> > > > >
> > > > > Thanks for your suggestions. I tried them but they did not work. I forgot
> > > > > to mention that the data may not always be sorted by date. I hope that does
> > > > > not affect your logic?
> > > > >
> > > > > To give you extra info on what went wrong: the formula of Don gave me an
> > > > > extremely high number. For example: I had data for one calendar year but the
> > > > > result of Don's formula was more than 366. This is not possible because
> > > > > there are only 366 unique dates in a year. The formula of Ron returned zero
> > > > > as a result.
> > > > >
> > > > > Thanks.
> > > > >
> > > > > "Ron Coderre" wrote:
> > > > >
> > > > > > Try something like this:
> > > > > >
> > > > > > With this structure in A1:A16
> > > > > > Date Dept
> > > > > > 1/1/2007 A
> > > > > > 1/1/2007 A
> > > > > > 1/1/2007 A
> > > > > > 1/1/2007 B
> > > > > > 1/1/2007 C
> > > > > > 1/1/2007 C
> > > > > > 1/1/2007 C
> > > > > > 1/15/2007 A
> > > > > > 1/15/2007 B
> > > > > > 1/15/2007 C
> > > > > > 1/15/2007 C
> > > > > > 2/1/2007 A
> > > > > > 2/1/2007 A
> > > > > > 2/1/2007 B
> > > > > > 2/1/2007 B
> > > > > >
> > > > > > And...
> > > > > > E1: (StartDate eg: 01/01/2007)
> > > > > > F1: (EndDate eg: 01/31/2007)
> > > > > > G1: (DeptName eg: A)
> > > > > >
> > > > > > Then this formula returns the number of unique dates in that range where the
> > > > > > DeptName matches the name in G1:
> > > > > > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > > > >
> > > > > > Using the above example, the formula returns: 2
> > > > > > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > > > >
> > > > > > Is that something you can work with?
> > > > > > ***********
> > > > > > Regards,
> > > > > > Ron
> > > > > >
> > > > > > XL2003, WinXP
> > > > > >
> > > > > >
> > > > > > "DKS" wrote:
> > > > > >
> > > > > > > I have a sheet, with each row containing one record. Column A contains a
> > > > > > > date, and column B contains a department. The contents of the column A
> > > > > > > (date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.
> > > > > > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column
> > > > > > > A + Column B.
> > > > > > >
> > > > > > > I needed (ideally) a worksheet function that would allow me to obtain the
> > > > > > > following information:
> > > > > > >
> > > > > > > Based on a START DATE, END DATE and DEPT NAME return to me the number of
> > > > > > > times I have the DEPT NAME occuring in my data for between the START DATE and
> > > > > > > END DATE (both dates included) but counting multiple rows for the same date
> > > > > > > as only 1 record. Thus if I have 27 rows for the same department with date
> > > > > > > July 15, then it should give me only the value 1.
> > > > > > >
> > > > > > > The time-part in the timestamp format can be ignored. It exists because of
> > > > > > > input data coming from various sources and some sources store time and some
> > > > > > > do not store time.
> > > > > > >
> > > > > > > How could I do the above with a worksheet function?
> > > > > > >
> > > > > > > Many thanks.
|
| |
|
| |
|
orchid11652
|
| Posted: Tue Jul 24 15:24:02 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Hi - o.k. - I can't help asking because your formula is so close to
something I need. See my pasted data below:
Event CodePlex Service ID Dates Scheduled*
EART309 SHO SH2 6/7/2006, 1/5/2007
EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
6/7/2006
EART309 WOM WOM 12/9/2006
EART312 SHO SH2 6/7/2006, 1/6/2007
EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
3/31/2006, 1/6/2007
EART312 WOM WOM 12/12/2006
Some of the data is wrapped so I'll explain the format. There are 4 fields -
an Event, Plex, Service ID and Date. Above is the exact excel export of the
data from an auxiliary system. The dates are populated into a single excel
field, however, I can parse them to populate a single date per field if it
makes the formula easier. What I need is a formula that totals all the
unique dates for each Event Code/Plex combination. The challenge is that
there can be multiple rows with the same Event Code and Plex, each which may
have the same date(s) which may only be counted once. In the above sample
data, the formula should return a quantity of 8 for the combination of Event
Code/Plex "EART309/SHO" and 8 for "EART312/SHO". I thought maybe an Access DB
would work better since I need to download the date data and create ITD
reports on a regular basis by Event. Any advice on if a formula or Access
would be better would be appreciated as well.
Thanks.
Orchid11652
> EMail@HideDomain.com
> "T. Valko" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
> > Congrats, Don!
> >
> > I has assumed you were already but didn't make it public.
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "Don Guillett" <EMail@HideDomain.com> wrote in message
> > news:EMail@HideDomain.com...
> >>I was saying that yours was the way to go and "Gracias Senor" for the kind
> >>words.
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> EMail@HideDomain.com
> >> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> >> news:EMail@HideDomain.com...
> >>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
> >>> :)
> >>>
> >>> (BTW......Congratulations on the MVP award)
> >>>
> >>> ***********
> >>> Regards,
> >>> Ron
> >>>
> >>> XL2003, WinXP
> >>>
> >>>
> >>> "Don Guillett" wrote:
> >>>
> >>>> I didn't see that you only wanted UNIQUE dates.
> >>>>
> >>>> --
> >>>> Don Guillett
> >>>> Microsoft MVP Excel
> >>>> SalesAid Software
> >>>> EMail@HideDomain.com
> >>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> >>>> news:EMail@HideDomain.com...
> >>>> >I tried scrambling the dates and DeptNames and I still get correct (to
> >>>> >me)
> >>>> > results.
> >>>> >
> >>>> > Try this:
> >>>> > Play with my posted example and see if it gives correct results under
> >>>> > those
> >>>> > conditions.
> >>>> > -If yes
> >>>> > ...Try putting some of your data into the example and test again.
> >>>> > -If No, then the data is the problem and you'll need to post some
> >>>> > sample
> >>>> > data so we can see what you're dealing with?
> >>>> >
> >>>> > Either way, please update us on your progress.
> >>>> >
> >>>> > ***********
> >>>> > Regards,
> >>>> > Ron
> >>>> >
> >>>> > XL2003, WinXP
> >>>> >
> >>>> >
> >>>> > "DKS" wrote:
> >>>> >
> >>>> >> Hi Don, Ron
> >>>> >>
> >>>> >> Thanks for your suggestions. I tried them but they did not work. I
> >>>> >> forgot
> >>>> >> to mention that the data may not always be sorted by date. I hope
> >>>> >> that
> >>>> >> does
> >>>> >> not affect your logic?
> >>>> >>
> >>>> >> To give you extra info on what went wrong: the formula of Don gave
> >>>> >> me an
> >>>> >> extremely high number. For example: I had data for one calendar
> >>>> >> year but
> >>>> >> the
> >>>> >> result of Don's formula was more than 366. This is not possible
> >>>> >> because
> >>>> >> there are only 366 unique dates in a year. The formula of Ron
> >>>> >> returned
> >>>> >> zero
> >>>> >> as a result.
> >>>> >>
> >>>> >> Thanks.
> >>>> >>
> >>>> >> "Ron Coderre" wrote:
> >>>> >>
> >>>> >> > Try something like this:
> >>>> >> >
> >>>> >> > With this structure in A1:A16
> >>>> >> > Date Dept
> >>>> >> > 1/1/2007 A
> >>>> >> > 1/1/2007 A
> >>>> >> > 1/1/2007 A
> >>>> >> > 1/1/2007 B
> >>>> >> > 1/1/2007 C
> >>>> >> > 1/1/2007 C
> >>>> >> > 1/1/2007 C
> >>>> >> > 1/15/2007 A
> >>>> >> > 1/15/2007 B
> >>>> >> > 1/15/2007 C
> >>>> >> > 1/15/2007 C
> >>>> >> > 2/1/2007 A
> >>>> >> > 2/1/2007 A
> >>>> >> > 2/1/2007 B
> >>>> >> > 2/1/2007 B
> >>>> >> >
> >>>> >> > And...
> >>>> >> > E1: (StartDate eg: 01/01/2007)
> >>>> >> > F1: (EndDate eg: 01/31/2007)
> >>>> >> > G1: (DeptName eg: A)
> >>>> >> >
> >>>> >> > Then this formula returns the number of unique dates in that range
> >>>> >> > where the
> >>>> >> > DeptName matches the name in G1:
> >>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> >>>> >> >
> >>>> >> > Using the above example, the formula returns: 2
> >>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> >>>> >> >
> >>>> >> > Is that something you can work with?
> >>>> >> > ***********
> >>>> >> > Regards,
> >>>> >> > Ron
> >>>> >> >
> >>>> >> > XL2003, WinXP
> >>>> >> >
> >>>> >> >
> >>>> >> > "DKS" wrote:
> >>>> >> >
> >>>> >> > > I have a sheet, with each row containing one record. Column A
> >>>> >> > > contains a
> >>>> >> > > date, and column B contains a department. The contents of the
> >>>> >> > > column
> >>>> >> > > A
> >>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
> >>>> >> > > timestamp
> >>>> >> > > e.g.
> >>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
> >>>> >> > > Column B,
> >>>> >> > > Column
> >>>> >> > > A + Column B.
> >>>> >> > >
> >>>> >> > > I needed (ideally) a worksheet function that would allow me to
> >>>> >> > > obtain
> >>>> >> > > the
> >>>> >> > > following information:
> >>>> >> > >
> >>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
> >>>> >> > > number
> >>>> >> > > of
> >>>> >> > > times I have the DEPT NAME occuring in my data for between the
> >>>> >> > > START
> >>>> >> > > DATE and
> >>>> >> > > END DATE (both dates included) but counting multiple rows for
> >>>> >> > > the
> >>>> >> > > same date
> >>>> >> > > as only 1 record. Thus if I have 27 rows for the same
> >>>> >> > > department
> >>>> >> > > with date
> >>>> >> > > July 15, then it should give me only the value 1.
> >>>> >> > >
> >>>> >> > > The time-part in the timestamp format can be ignored. It exists
> >>>> >> > > because of
> >>>> >> > > input data coming from various sources and some sources store
> >>>> >> > > time
> >>>> >> > > and some
> >>>> >> > > do not store time.
> >>>> >> > >
> >>>> >> > > How could I do the above with a worksheet function?
> >>>> >> > >
> >>>> >> > > Many thanks.
> >>>>
> >>>>
> >>
> >
> >
>
>
|
| |
|
| |
|
RonCoderre
|
| Posted: Tue Jul 24 18:00:01 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
I think I came up with something that works......
With
your posted sample data in A1:D7 (I'll break it up to avoid Text Wrap):
A1:C7 contains:
Event CodePlex Service ID
EART309 SHO SH2
EART309 SHO SHO
EART309 WOM WOM
EART312 SHO SH2
EART312 SHO SHO
EART312 WOM WOM
D1:D7 contains:
Dates Scheduled*
6/7/2006, 1/5/2007
3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006, 6/7/2006
12/9/2006
6/7/2006, 1/6/2007
3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006, 3/31/2006, 1/6/2007
12/12/2006
AND....
E1: (StartDate....of the range of dates to be searched, eg 3/1/2006)
F1: (EndDate....of the range of dates to be searched, eg 12/31/2007)
Then....try this:
G1: (an event, eg EART309)
H1: (a codeplex, eg SHO)
This ARRAY FORMULA returns the count of unique dates within the start/end
range for the Event/CodePlex combination in G1:H1
I1:
=SUM(--NOT(ISNA(MATCH("*"&TEXT(ROW(INDEX($A:$A,$E$1):INDEX($A:$A,$F$1)),"m/d/yyyy")&"*",IF($A$2:$A$7&$B$2:$B$7=G1&H1,$D$2:$D$7),0))))
Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
pressing [Enter].
With sample tests, these are the results:
EART309, SHO.........Returns 8
EART309, WOM........Returns 1
EART312, SHO.........Returns 8
Is that something you can work with?
***********
Regards,
Ron
XL2003, WinXP
"orchid11652" wrote:
>
>
> Hi - o.k. - I can't help asking because your formula is so close to
> something I need. See my pasted data below:
>
> Event CodePlex Service ID Dates Scheduled*
> EART309 SHO SH2 6/7/2006, 1/5/2007
> EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
> 6/7/2006
>
> EART309 WOM WOM 12/9/2006
> EART312 SHO SH2 6/7/2006, 1/6/2007
> EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
> 3/31/2006, 1/6/2007
>
> EART312 WOM WOM 12/12/2006
>
>
> Some of the data is wrapped so I'll explain the format. There are 4 fields -
> an Event, Plex, Service ID and Date. Above is the exact excel export of the
> data from an auxiliary system. The dates are populated into a single excel
> field, however, I can parse them to populate a single date per field if it
> makes the formula easier. What I need is a formula that totals all the
> unique dates for each Event Code/Plex combination. The challenge is that
> there can be multiple rows with the same Event Code and Plex, each which may
> have the same date(s) which may only be counted once. In the above sample
> data, the formula should return a quantity of 8 for the combination of Event
> Code/Plex "EART309/SHO" and 8 for "EART312/SHO". I thought maybe an Access DB
> would work better since I need to download the date data and create ITD
> reports on a regular basis by Event. Any advice on if a formula or Access
> would be better would be appreciated as well.
>
> Thanks.
>
> Orchid11652
>
>
>
>
>
> > EMail@HideDomain.com
> > "T. Valko" <EMail@HideDomain.com> wrote in message
> > news:EMail@HideDomain.com...
> > > Congrats, Don!
> > >
> > > I has assumed you were already but didn't make it public.
> > >
> > > --
> > > Biff
> > > Microsoft Excel MVP
> > >
> > >
> > > "Don Guillett" <EMail@HideDomain.com> wrote in message
> > > news:EMail@HideDomain.com...
> > >>I was saying that yours was the way to go and "Gracias Senor" for the kind
> > >>words.
> > >>
> > >> --
> > >> Don Guillett
> > >> Microsoft MVP Excel
> > >> SalesAid Software
> > >> EMail@HideDomain.com
> > >> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > >> news:EMail@HideDomain.com...
> > >>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
> > >>> :)
> > >>>
> > >>> (BTW......Congratulations on the MVP award)
> > >>>
> > >>> ***********
> > >>> Regards,
> > >>> Ron
> > >>>
> > >>> XL2003, WinXP
> > >>>
> > >>>
> > >>> "Don Guillett" wrote:
> > >>>
> > >>>> I didn't see that you only wanted UNIQUE dates.
> > >>>>
> > >>>> --
> > >>>> Don Guillett
> > >>>> Microsoft MVP Excel
> > >>>> SalesAid Software
> > >>>> EMail@HideDomain.com
> > >>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > >>>> news:EMail@HideDomain.com...
> > >>>> >I tried scrambling the dates and DeptNames and I still get correct (to
> > >>>> >me)
> > >>>> > results.
> > >>>> >
> > >>>> > Try this:
> > >>>> > Play with my posted example and see if it gives correct results under
> > >>>> > those
> > >>>> > conditions.
> > >>>> > -If yes
> > >>>> > ...Try putting some of your data into the example and test again.
> > >>>> > -If No, then the data is the problem and you'll need to post some
> > >>>> > sample
> > >>>> > data so we can see what you're dealing with?
> > >>>> >
> > >>>> > Either way, please update us on your progress.
> > >>>> >
> > >>>> > ***********
> > >>>> > Regards,
> > >>>> > Ron
> > >>>> >
> > >>>> > XL2003, WinXP
> > >>>> >
> > >>>> >
> > >>>> > "DKS" wrote:
> > >>>> >
> > >>>> >> Hi Don, Ron
> > >>>> >>
> > >>>> >> Thanks for your suggestions. I tried them but they did not work. I
> > >>>> >> forgot
> > >>>> >> to mention that the data may not always be sorted by date. I hope
> > >>>> >> that
> > >>>> >> does
> > >>>> >> not affect your logic?
> > >>>> >>
> > >>>> >> To give you extra info on what went wrong: the formula of Don gave
> > >>>> >> me an
> > >>>> >> extremely high number. For example: I had data for one calendar
> > >>>> >> year but
> > >>>> >> the
> > >>>> >> result of Don's formula was more than 366. This is not possible
> > >>>> >> because
> > >>>> >> there are only 366 unique dates in a year. The formula of Ron
> > >>>> >> returned
> > >>>> >> zero
> > >>>> >> as a result.
> > >>>> >>
> > >>>> >> Thanks.
> > >>>> >>
> > >>>> >> "Ron Coderre" wrote:
> > >>>> >>
> > >>>> >> > Try something like this:
> > >>>> >> >
> > >>>> >> > With this structure in A1:A16
> > >>>> >> > Date Dept
> > >>>> >> > 1/1/2007 A
> > >>>> >> > 1/1/2007 A
> > >>>> >> > 1/1/2007 A
> > >>>> >> > 1/1/2007 B
> > >>>> >> > 1/1/2007 C
> > >>>> >> > 1/1/2007 C
> > >>>> >> > 1/1/2007 C
> > >>>> >> > 1/15/2007 A
> > >>>> >> > 1/15/2007 B
> > >>>> >> > 1/15/2007 C
> > >>>> >> > 1/15/2007 C
> > >>>> >> > 2/1/2007 A
> > >>>> >> > 2/1/2007 A
> > >>>> >> > 2/1/2007 B
> > >>>> >> > 2/1/2007 B
> > >>>> >> >
> > >>>> >> > And...
> > >>>> >> > E1: (StartDate eg: 01/01/2007)
> > >>>> >> > F1: (EndDate eg: 01/31/2007)
> > >>>> >> > G1: (DeptName eg: A)
> > >>>> >> >
> > >>>> >> > Then this formula returns the number of unique dates in that range
> > >>>> >> > where the
> > >>>> >> > DeptName matches the name in G1:
> > >>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > >>>> >> >
> > >>>> >> > Using the above example, the formula returns: 2
> > >>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > >>>> >> >
> > >>>> >> > Is that something you can work with?
> > >>>> >> > ***********
> > >>>> >> > Regards,
> > >>>> >> > Ron
> > >>>> >> >
> > >>>> >> > XL2003, WinXP
> > >>>> >> >
> > >>>> >> >
> > >>>> >> > "DKS" wrote:
> > >>>> >> >
> > >>>> >> > > I have a sheet, with each row containing one record. Column A
> > >>>> >> > > contains a
> > >>>> >> > > date, and column B contains a department. The contents of the
> > >>>> >> > > column
> > >>>> >> > > A
> > >>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
> > >>>> >> > > timestamp
> > >>>> >> > > e.g.
> > >>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
> > >>>> >> > > Column B,
> > >>>> >> > > Column
> > >>>> >> > > A + Column B.
> > >>>> >> > >
> > >>>> >> > > I needed (ideally) a worksheet function that would allow me to
> > >>>> >> > > obtain
> > >>>> >> > > the
> > >>>> >> > > following information:
> > >>>> >> > >
> > >>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
> > >>>> >> > > number
> > >>>> >> > > of
> > >>>> >> > > times I have the DEPT NAME occuring in my data for between the
> > >>>> >> > > START
> > >>>> >> > > DATE and
> > >>>> >> > > END DATE (both dates included) but counting multiple rows for
> > >>>> >> > > the
> > >>>> >> > > same date
> > >>>> >> > > as only 1 record. Thus if I have 27 rows for the same
> > >>>> >> > > department
> > >>>> >> > > with date
> > >>>> >> > > July 15, then it should give me only the value 1.
> > >>>> >> > >
> > >>>> >> > > The time-part in the timestamp format can be ignored. It exists
> > >>>> >> > > because of
> > >>>> >> > > input data coming from various sources and some sources store
> > >>>> >> > > time
> > >>>> >> > > and some
> > >>>> >> > > do not store time.
> > >>>> >> > >
> > >>>> >> > > How could I do the above with a worksheet function?
> > >>>> >> > >
> > >>>> >> > > Many thanks.
> > >>>>
> > >>>>
> > >>
> > >
> > >
> >
> >
|
| |
|
| |
|
orchid11652
|
| Posted: Tue Jul 24 20:16:00 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Thanks! I will try it and let you know.
"Ron Coderre" wrote:
> I think I came up with something that works......
>
> With
> your posted sample data in A1:D7 (I'll break it up to avoid Text Wrap):
> A1:C7 contains:
> Event CodePlex Service ID
> EART309 SHO SH2
> EART309 SHO SHO
> EART309 WOM WOM
> EART312 SHO SH2
> EART312 SHO SHO
> EART312 WOM WOM
>
> D1:D7 contains:
> Dates Scheduled*
> 6/7/2006, 1/5/2007
> 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006, 6/7/2006
> 12/9/2006
> 6/7/2006, 1/6/2007
> 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006, 3/31/2006, 1/6/2007
> 12/12/2006
>
> AND....
> E1: (StartDate....of the range of dates to be searched, eg 3/1/2006)
> F1: (EndDate....of the range of dates to be searched, eg 12/31/2007)
>
> Then....try this:
>
> G1: (an event, eg EART309)
> H1: (a codeplex, eg SHO)
>
> This ARRAY FORMULA returns the count of unique dates within the start/end
> range for the Event/CodePlex combination in G1:H1
> I1:
> =SUM(--NOT(ISNA(MATCH("*"&TEXT(ROW(INDEX($A:$A,$E$1):INDEX($A:$A,$F$1)),"m/d/yyyy")&"*",IF($A$2:$A$7&$B$2:$B$7=G1&H1,$D$2:$D$7),0))))
>
> Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
> pressing [Enter].
>
> With sample tests, these are the results:
> EART309, SHO.........Returns 8
> EART309, WOM........Returns 1
> EART312, SHO.........Returns 8
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "orchid11652" wrote:
>
> >
> >
> > Hi - o.k. - I can't help asking because your formula is so close to
> > something I need. See my pasted data below:
> >
> > Event CodePlex Service ID Dates Scheduled*
> > EART309 SHO SH2 6/7/2006, 1/5/2007
> > EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
> > 6/7/2006
> >
> > EART309 WOM WOM 12/9/2006
> > EART312 SHO SH2 6/7/2006, 1/6/2007
> > EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
> > 3/31/2006, 1/6/2007
> >
> > EART312 WOM WOM 12/12/2006
> >
> >
> > Some of the data is wrapped so I'll explain the format. There are 4 fields -
> > an Event, Plex, Service ID and Date. Above is the exact excel export of the
> > data from an auxiliary system. The dates are populated into a single excel
> > field, however, I can parse them to populate a single date per field if it
> > makes the formula easier. What I need is a formula that totals all the
> > unique dates for each Event Code/Plex combination. The challenge is that
> > there can be multiple rows with the same Event Code and Plex, each which may
> > have the same date(s) which may only be counted once. In the above sample
> > data, the formula should return a quantity of 8 for the combination of Event
> > Code/Plex "EART309/SHO" and 8 for "EART312/SHO". I thought maybe an Access DB
> > would work better since I need to download the date data and create ITD
> > reports on a regular basis by Event. Any advice on if a formula or Access
> > would be better would be appreciated as well.
> >
> > Thanks.
> >
> > Orchid11652
> >
> >
> >
> >
> >
> > > EMail@HideDomain.com
> > > "T. Valko" <EMail@HideDomain.com> wrote in message
> > > news:EMail@HideDomain.com...
> > > > Congrats, Don!
> > > >
> > > > I has assumed you were already but didn't make it public.
> > > >
> > > > --
> > > > Biff
> > > > Microsoft Excel MVP
> > > >
> > > >
> > > > "Don Guillett" <EMail@HideDomain.com> wrote in message
> > > > news:EMail@HideDomain.com...
> > > >>I was saying that yours was the way to go and "Gracias Senor" for the kind
> > > >>words.
> > > >>
> > > >> --
> > > >> Don Guillett
> > > >> Microsoft MVP Excel
> > > >> SalesAid Software
> > > >> EMail@HideDomain.com
> > > >> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > >> news:EMail@HideDomain.com...
> > > >>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
> > > >>> :)
> > > >>>
> > > >>> (BTW......Congratulations on the MVP award)
> > > >>>
> > > >>> ***********
> > > >>> Regards,
> > > >>> Ron
> > > >>>
> > > >>> XL2003, WinXP
> > > >>>
> > > >>>
> > > >>> "Don Guillett" wrote:
> > > >>>
> > > >>>> I didn't see that you only wanted UNIQUE dates.
> > > >>>>
> > > >>>> --
> > > >>>> Don Guillett
> > > >>>> Microsoft MVP Excel
> > > >>>> SalesAid Software
> > > >>>> EMail@HideDomain.com
> > > >>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > >>>> news:EMail@HideDomain.com...
> > > >>>> >I tried scrambling the dates and DeptNames and I still get correct (to
> > > >>>> >me)
> > > >>>> > results.
> > > >>>> >
> > > >>>> > Try this:
> > > >>>> > Play with my posted example and see if it gives correct results under
> > > >>>> > those
> > > >>>> > conditions.
> > > >>>> > -If yes
> > > >>>> > ...Try putting some of your data into the example and test again.
> > > >>>> > -If No, then the data is the problem and you'll need to post some
> > > >>>> > sample
> > > >>>> > data so we can see what you're dealing with?
> > > >>>> >
> > > >>>> > Either way, please update us on your progress.
> > > >>>> >
> > > >>>> > ***********
> > > >>>> > Regards,
> > > >>>> > Ron
> > > >>>> >
> > > >>>> > XL2003, WinXP
> > > >>>> >
> > > >>>> >
> > > >>>> > "DKS" wrote:
> > > >>>> >
> > > >>>> >> Hi Don, Ron
> > > >>>> >>
> > > >>>> >> Thanks for your suggestions. I tried them but they did not work. I
> > > >>>> >> forgot
> > > >>>> >> to mention that the data may not always be sorted by date. I hope
> > > >>>> >> that
> > > >>>> >> does
> > > >>>> >> not affect your logic?
> > > >>>> >>
> > > >>>> >> To give you extra info on what went wrong: the formula of Don gave
> > > >>>> >> me an
> > > >>>> >> extremely high number. For example: I had data for one calendar
> > > >>>> >> year but
> > > >>>> >> the
> > > >>>> >> result of Don's formula was more than 366. This is not possible
> > > >>>> >> because
> > > >>>> >> there are only 366 unique dates in a year. The formula of Ron
> > > >>>> >> returned
> > > >>>> >> zero
> > > >>>> >> as a result.
> > > >>>> >>
> > > >>>> >> Thanks.
> > > >>>> >>
> > > >>>> >> "Ron Coderre" wrote:
> > > >>>> >>
> > > >>>> >> > Try something like this:
> > > >>>> >> >
> > > >>>> >> > With this structure in A1:A16
> > > >>>> >> > Date Dept
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 B
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/15/2007 A
> > > >>>> >> > 1/15/2007 B
> > > >>>> >> > 1/15/2007 C
> > > >>>> >> > 1/15/2007 C
> > > >>>> >> > 2/1/2007 A
> > > >>>> >> > 2/1/2007 A
> > > >>>> >> > 2/1/2007 B
> > > >>>> >> > 2/1/2007 B
> > > >>>> >> >
> > > >>>> >> > And...
> > > >>>> >> > E1: (StartDate eg: 01/01/2007)
> > > >>>> >> > F1: (EndDate eg: 01/31/2007)
> > > >>>> >> > G1: (DeptName eg: A)
> > > >>>> >> >
> > > >>>> >> > Then this formula returns the number of unique dates in that range
> > > >>>> >> > where the
> > > >>>> >> > DeptName matches the name in G1:
> > > >>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > >>>> >> >
> > > >>>> >> > Using the above example, the formula returns: 2
> > > >>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > >>>> >> >
> > > >>>> >> > Is that something you can work with?
> > > >>>> >> > ***********
> > > >>>> >> > Regards,
> > > >>>> >> > Ron
> > > >>>> >> >
> > > >>>> >> > XL2003, WinXP
> > > >>>> >> >
> > > >>>> >> >
> > > >>>> >> > "DKS" wrote:
> > > >>>> >> >
> > > >>>> >> > > I have a sheet, with each row containing one record. Column A
> > > >>>> >> > > contains a
> > > >>>> >> > > date, and column B contains a department. The contents of the
> > > >>>> >> > > column
> > > >>>> >> > > A
> > > >>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
> > > >>>> >> > > timestamp
> > > >>>> >> > > e.g.
> > > >>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
> > > >>>> >> > > Column B,
> > > >>>> >> > > Column
> > > >>>> >> > > A + Column B.
> > > >>>> >> > >
> > > >>>> >> > > I needed (ideally) a worksheet function that would allow me to
> > > >>>> >> > > obtain
> > > >>>> >> > > the
> > > >>>> >> > > following information:
> > > >>>> >> > >
> > > >>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
> > > >>>> >> > > number
> > > >>>> >> > > of
> > > >>>> >> > > times I have the DEPT NAME occuring in my data for between the
> > > >>>> >> > > START
> > > >>>> >> > > DATE and
> > > >>>> >> > > END DATE (both dates included) but counting multiple rows for
> > > >>>> >> > > the
> > > >>>> >> > > same date
> > > >>>> >> > > as only 1 record. Thus if I have 27 rows for the same
> > > >>>> >> > > department
> > > >>>> >> > > with date
> > > >>>> >> > > July 15, then it should give me only the value 1.
> > > >>>> >> > >
> > > >>>> >> > > The time-part in the timestamp format can be ignored. It exists
> > > >>>> >> > > because of
> > > >>>> >> > > input data coming from various sources and some sources store
> > > >>>> >> > > time
> > > >>>> >> > > and some
> > > >>>> >> > > do not store time.
> > > >>>> >> > >
> > > >>>> >> > > How could I do the above with a worksheet function?
> > > >>>> >> > >
> > > >>>> >> > > Many thanks.
> > > >>>>
> > > >>>>
> > > >>
> > > >
> > > >
> > >
> > >
|
| |
|
| |
|
orchid11652
|
| Posted: Wed Jul 25 15:54:04 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
Hi - I tried your example but I'm having trouble replicating the result for
'EART309,WOM' or the other combination with only a single date. It is giving
me a zero. If I populate an additional date, it gives the correct count of
2. I will work with it and see if I set up the format correctly. Thanks!
This was very helpful.
"Ron Coderre" wrote:
> I think I came up with something that works......
>
> With
> your posted sample data in A1:D7 (I'll break it up to avoid Text Wrap):
> A1:C7 contains:
> Event CodePlex Service ID
> EART309 SHO SH2
> EART309 SHO SHO
> EART309 WOM WOM
> EART312 SHO SH2
> EART312 SHO SHO
> EART312 WOM WOM
>
> D1:D7 contains:
> Dates Scheduled*
> 6/7/2006, 1/5/2007
> 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006, 6/7/2006
> 12/9/2006
> 6/7/2006, 1/6/2007
> 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006, 3/31/2006, 1/6/2007
> 12/12/2006
>
> AND....
> E1: (StartDate....of the range of dates to be searched, eg 3/1/2006)
> F1: (EndDate....of the range of dates to be searched, eg 12/31/2007)
>
> Then....try this:
>
> G1: (an event, eg EART309)
> H1: (a codeplex, eg SHO)
>
> This ARRAY FORMULA returns the count of unique dates within the start/end
> range for the Event/CodePlex combination in G1:H1
> I1:
> =SUM(--NOT(ISNA(MATCH("*"&TEXT(ROW(INDEX($A:$A,$E$1):INDEX($A:$A,$F$1)),"m/d/yyyy")&"*",IF($A$2:$A$7&$B$2:$B$7=G1&H1,$D$2:$D$7),0))))
>
> Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
> pressing [Enter].
>
> With sample tests, these are the results:
> EART309, SHO.........Returns 8
> EART309, WOM........Returns 1
> EART312, SHO.........Returns 8
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2003, WinXP
>
>
> "orchid11652" wrote:
>
> >
> >
> > Hi - o.k. - I can't help asking because your formula is so close to
> > something I need. See my pasted data below:
> >
> > Event CodePlex Service ID Dates Scheduled*
> > EART309 SHO SH2 6/7/2006, 1/5/2007
> > EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
> > 6/7/2006
> >
> > EART309 WOM WOM 12/9/2006
> > EART312 SHO SH2 6/7/2006, 1/6/2007
> > EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
> > 3/31/2006, 1/6/2007
> >
> > EART312 WOM WOM 12/12/2006
> >
> >
> > Some of the data is wrapped so I'll explain the format. There are 4 fields -
> > an Event, Plex, Service ID and Date. Above is the exact excel export of the
> > data from an auxiliary system. The dates are populated into a single excel
> > field, however, I can parse them to populate a single date per field if it
> > makes the formula easier. What I need is a formula that totals all the
> > unique dates for each Event Code/Plex combination. The challenge is that
> > there can be multiple rows with the same Event Code and Plex, each which may
> > have the same date(s) which may only be counted once. In the above sample
> > data, the formula should return a quantity of 8 for the combination of Event
> > Code/Plex "EART309/SHO" and 8 for "EART312/SHO". I thought maybe an Access DB
> > would work better since I need to download the date data and create ITD
> > reports on a regular basis by Event. Any advice on if a formula or Access
> > would be better would be appreciated as well.
> >
> > Thanks.
> >
> > Orchid11652
> >
> >
> >
> >
> >
> > > EMail@HideDomain.com
> > > "T. Valko" <EMail@HideDomain.com> wrote in message
> > > news:EMail@HideDomain.com...
> > > > Congrats, Don!
> > > >
> > > > I has assumed you were already but didn't make it public.
> > > >
> > > > --
> > > > Biff
> > > > Microsoft Excel MVP
> > > >
> > > >
> > > > "Don Guillett" <EMail@HideDomain.com> wrote in message
> > > > news:EMail@HideDomain.com...
> > > >>I was saying that yours was the way to go and "Gracias Senor" for the kind
> > > >>words.
> > > >>
> > > >> --
> > > >> Don Guillett
> > > >> Microsoft MVP Excel
> > > >> SalesAid Software
> > > >> EMail@HideDomain.com
> > > >> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > >> news:EMail@HideDomain.com...
> > > >>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
> > > >>> :)
> > > >>>
> > > >>> (BTW......Congratulations on the MVP award)
> > > >>>
> > > >>> ***********
> > > >>> Regards,
> > > >>> Ron
> > > >>>
> > > >>> XL2003, WinXP
> > > >>>
> > > >>>
> > > >>> "Don Guillett" wrote:
> > > >>>
> > > >>>> I didn't see that you only wanted UNIQUE dates.
> > > >>>>
> > > >>>> --
> > > >>>> Don Guillett
> > > >>>> Microsoft MVP Excel
> > > >>>> SalesAid Software
> > > >>>> EMail@HideDomain.com
> > > >>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > >>>> news:EMail@HideDomain.com...
> > > >>>> >I tried scrambling the dates and DeptNames and I still get correct (to
> > > >>>> >me)
> > > >>>> > results.
> > > >>>> >
> > > >>>> > Try this:
> > > >>>> > Play with my posted example and see if it gives correct results under
> > > >>>> > those
> > > >>>> > conditions.
> > > >>>> > -If yes
> > > >>>> > ...Try putting some of your data into the example and test again.
> > > >>>> > -If No, then the data is the problem and you'll need to post some
> > > >>>> > sample
> > > >>>> > data so we can see what you're dealing with?
> > > >>>> >
> > > >>>> > Either way, please update us on your progress.
> > > >>>> >
> > > >>>> > ***********
> > > >>>> > Regards,
> > > >>>> > Ron
> > > >>>> >
> > > >>>> > XL2003, WinXP
> > > >>>> >
> > > >>>> >
> > > >>>> > "DKS" wrote:
> > > >>>> >
> > > >>>> >> Hi Don, Ron
> > > >>>> >>
> > > >>>> >> Thanks for your suggestions. I tried them but they did not work. I
> > > >>>> >> forgot
> > > >>>> >> to mention that the data may not always be sorted by date. I hope
> > > >>>> >> that
> > > >>>> >> does
> > > >>>> >> not affect your logic?
> > > >>>> >>
> > > >>>> >> To give you extra info on what went wrong: the formula of Don gave
> > > >>>> >> me an
> > > >>>> >> extremely high number. For example: I had data for one calendar
> > > >>>> >> year but
> > > >>>> >> the
> > > >>>> >> result of Don's formula was more than 366. This is not possible
> > > >>>> >> because
> > > >>>> >> there are only 366 unique dates in a year. The formula of Ron
> > > >>>> >> returned
> > > >>>> >> zero
> > > >>>> >> as a result.
> > > >>>> >>
> > > >>>> >> Thanks.
> > > >>>> >>
> > > >>>> >> "Ron Coderre" wrote:
> > > >>>> >>
> > > >>>> >> > Try something like this:
> > > >>>> >> >
> > > >>>> >> > With this structure in A1:A16
> > > >>>> >> > Date Dept
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 A
> > > >>>> >> > 1/1/2007 B
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/1/2007 C
> > > >>>> >> > 1/15/2007 A
> > > >>>> >> > 1/15/2007 B
> > > >>>> >> > 1/15/2007 C
> > > >>>> >> > 1/15/2007 C
> > > >>>> >> > 2/1/2007 A
> > > >>>> >> > 2/1/2007 A
> > > >>>> >> > 2/1/2007 B
> > > >>>> >> > 2/1/2007 B
> > > >>>> >> >
> > > >>>> >> > And...
> > > >>>> >> > E1: (StartDate eg: 01/01/2007)
> > > >>>> >> > F1: (EndDate eg: 01/31/2007)
> > > >>>> >> > G1: (DeptName eg: A)
> > > >>>> >> >
> > > >>>> >> > Then this formula returns the number of unique dates in that range
> > > >>>> >> > where the
> > > >>>> >> > DeptName matches the name in G1:
> > > >>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > >>>> >> >
> > > >>>> >> > Using the above example, the formula returns: 2
> > > >>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > >>>> >> >
> > > >>>> >> > Is that something you can work with?
> > > >>>> >> > ***********
> > > >>>> >> > Regards,
> > > >>>> >> > Ron
> > > >>>> >> >
> > > >>>> >> > XL2003, WinXP
> > > >>>> >> >
> > > >>>> >> >
> > > >>>> >> > "DKS" wrote:
> > > >>>> >> >
> > > >>>> >> > > I have a sheet, with each row containing one record. Column A
> > > >>>> >> > > contains a
> > > >>>> >> > > date, and column B contains a department. The contents of the
> > > >>>> >> > > column
> > > >>>> >> > > A
> > > >>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
> > > >>>> >> > > timestamp
> > > >>>> >> > > e.g.
> > > >>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
> > > >>>> >> > > Column B,
> > > >>>> >> > > Column
> > > >>>> >> > > A + Column B.
> > > >>>> >> > >
> > > >>>> >> > > I needed (ideally) a worksheet function that would allow me to
> > > >>>> >> > > obtain
> > > >>>> >> > > the
> > > >>>> >> > > following information:
> > > >>>> >> > >
> > > >>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
> > > >>>> >> > > number
> > > >>>> >> > > of
> > > >>>> >> > > times I have the DEPT NAME occuring in my data for between the
> > > >>>> >> > > START
> > > >>>> >> > > DATE and
> > > >>>> >> > > END DATE (both dates included) but counting multiple rows for
> > > >>>> >> > > the
> > > >>>> >> > > same date
> > > >>>> >> > > as only 1 record. Thus if I have 27 rows for the same
> > > >>>> >> > > department
> > > >>>> >> > > with date
> > > >>>> >> > > July 15, then it should give me only the value 1.
> > > >>>> >> > >
> > > >>>> >> > > The time-part in the timestamp format can be ignored. It exists
> > > >>>> >> > > because of
> > > >>>> >> > > input data coming from various sources and some sources store
> > > >>>> >> > > time
> > > >>>> >> > > and some
> > > >>>> >> > > do not store time.
> > > >>>> >> > >
> > > >>>> >> > > How could I do the above with a worksheet function?
> > > >>>> >> > >
> > > >>>> >> > > Many thanks.
> > > >>>>
> > > >>>>
> > > >>
> > > >
> > > >
> > >
> > >
|
| |
|
| |
|
RonCoderre
|
| Posted: Wed Jul 25 16:08:03 CDT 2007 |
Top |
worksheet functions >> Counting unique items based on date
The formula I posted searches for TEXT values. Consequently, when the "Dates
Scheduled*" field only has one "date" in it, you must make sure the value is
TEXT, not an actual date.
You can do that by either:
â?¢prepending an apostrophe in front of the date
(eg '03/01/2007 )
or
â?¢Setting the numeric format of the cells to Text before entering data.
<format><cells><number tab>....Category: Text
Does that help?
***********
Regards,
Ron
XL2003, WinXP
"orchid11652" wrote:
> Hi - I tried your example but I'm having trouble replicating the result for
> 'EART309,WOM' or the other combination with only a single date. It is giving
> me a zero. If I populate an additional date, it gives the correct count of
> 2. I will work with it and see if I set up the format correctly. Thanks!
> This was very helpful.
>
> "Ron Coderre" wrote:
>
> > I think I came up with something that works......
> >
> > With
> > your posted sample data in A1:D7 (I'll break it up to avoid Text Wrap):
> > A1:C7 contains:
> > Event CodePlex Service ID
> > EART309 SHO SH2
> > EART309 SHO SHO
> > EART309 WOM WOM
> > EART312 SHO SH2
> > EART312 SHO SHO
> > EART312 WOM WOM
> >
> > D1:D7 contains:
> > Dates Scheduled*
> > 6/7/2006, 1/5/2007
> > 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006, 6/7/2006
> > 12/9/2006
> > 6/7/2006, 1/6/2007
> > 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006, 3/31/2006, 1/6/2007
> > 12/12/2006
> >
> > AND....
> > E1: (StartDate....of the range of dates to be searched, eg 3/1/2006)
> > F1: (EndDate....of the range of dates to be searched, eg 12/31/2007)
> >
> > Then....try this:
> >
> > G1: (an event, eg EART309)
> > H1: (a codeplex, eg SHO)
> >
> > This ARRAY FORMULA returns the count of unique dates within the start/end
> > range for the Event/CodePlex combination in G1:H1
> > I1:
> > =SUM(--NOT(ISNA(MATCH("*"&TEXT(ROW(INDEX($A:$A,$E$1):INDEX($A:$A,$F$1)),"m/d/yyyy")&"*",IF($A$2:$A$7&$B$2:$B$7=G1&H1,$D$2:$D$7),0))))
> >
> > Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
> > pressing [Enter].
> >
> > With sample tests, these are the results:
> > EART309, SHO.........Returns 8
> > EART309, WOM........Returns 1
> > EART312, SHO.........Returns 8
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2003, WinXP
> >
> >
> > "orchid11652" wrote:
> >
> > >
> > >
> > > Hi - o.k. - I can't help asking because your formula is so close to
> > > something I need. See my pasted data below:
> > >
> > > Event CodePlex Service ID Dates Scheduled*
> > > EART309 SHO SH2 6/7/2006, 1/5/2007
> > > EART309 SHO SHO 3/5/2006, 3/6/2006, 3/7/2006, 3/8/2006, 3/9/2006, 3/10/2006,
> > > 6/7/2006
> > >
> > > EART309 WOM WOM 12/9/2006
> > > EART312 SHO SH2 6/7/2006, 1/6/2007
> > > EART312 SHO SHO 3/26/2006, 3/27/2006, 3/28/2006, 3/29/2006, 3/30/2006,
> > > 3/31/2006, 1/6/2007
> > >
> > > EART312 WOM WOM 12/12/2006
> > >
> > >
> > > Some of the data is wrapped so I'll explain the format. There are 4 fields -
> > > an Event, Plex, Service ID and Date. Above is the exact excel export of the
> > > data from an auxiliary system. The dates are populated into a single excel
> > > field, however, I can parse them to populate a single date per field if it
> > > makes the formula easier. What I need is a formula that totals all the
> > > unique dates for each Event Code/Plex combination. The challenge is that
> > > there can be multiple rows with the same Event Code and Plex, each which may
> > > have the same date(s) which may only be counted once. In the above sample
> > > data, the formula should return a quantity of 8 for the combination of Event
> > > Code/Plex "EART309/SHO" and 8 for "EART312/SHO". I thought maybe an Access DB
> > > would work better since I need to download the date data and create ITD
> > > reports on a regular basis by Event. Any advice on if a formula or Access
> > > would be better would be appreciated as well.
> > >
> > > Thanks.
> > >
> > > Orchid11652
> > >
> > >
> > >
> > >
> > >
> > > > EMail@HideDomain.com
> > > > "T. Valko" <EMail@HideDomain.com> wrote in message
> > > > news:EMail@HideDomain.com...
> > > > > Congrats, Don!
> > > > >
> > > > > I has assumed you were already but didn't make it public.
> > > > >
> > > > > --
> > > > > Biff
> > > > > Microsoft Excel MVP
> > > > >
> > > > >
> > > > > "Don Guillett" <EMail@HideDomain.com> wrote in message
> > > > > news:EMail@HideDomain.com...
> > > > >>I was saying that yours was the way to go and "Gracias Senor" for the kind
> > > > >>words.
> > > > >>
> > > > >> --
> > > > >> Don Guillett
> > > > >> Microsoft MVP Excel
> > > > >> SalesAid Software
> > > > >> EMail@HideDomain.com
> > > > >> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > > >> news:EMail@HideDomain.com...
> > > > >>> Not me, Don (I don't have a preference)....but DKS wants unique dates.
> > > > >>> :)
> > > > >>>
> > > > >>> (BTW......Congratulations on the MVP award)
> > > > >>>
> > > > >>> ***********
> > > > >>> Regards,
> > > > >>> Ron
> > > > >>>
> > > > >>> XL2003, WinXP
> > > > >>>
> > > > >>>
> > > > >>> "Don Guillett" wrote:
> > > > >>>
> > > > >>>> I didn't see that you only wanted UNIQUE dates.
> > > > >>>>
> > > > >>>> --
> > > > >>>> Don Guillett
> > > > >>>> Microsoft MVP Excel
> > > > >>>> SalesAid Software
> > > > >>>> EMail@HideDomain.com
> > > > >>>> "Ron Coderre" <EMail@HideDomain.com> wrote in message
> > > > >>>> news:EMail@HideDomain.com...
> > > > >>>> >I tried scrambling the dates and DeptNames and I still get correct (to
> > > > >>>> >me)
> > > > >>>> > results.
> > > > >>>> >
> > > > >>>> > Try this:
> > > > >>>> > Play with my posted example and see if it gives correct results under
> > > > >>>> > those
> > > > >>>> > conditions.
> > > > >>>> > -If yes
> > > > >>>> > ...Try putting some of your data into the example and test again.
> > > > >>>> > -If No, then the data is the problem and you'll need to post some
> > > > >>>> > sample
> > > > >>>> > data so we can see what you're dealing with?
> > > > >>>> >
> > > > >>>> > Either way, please update us on your progress.
> > > > >>>> >
> > > > >>>> > ***********
> > > > >>>> > Regards,
> > > > >>>> > Ron
> > > > >>>> >
> > > > >>>> > XL2003, WinXP
> > > > >>>> >
> > > > >>>> >
> > > > >>>> > "DKS" wrote:
> > > > >>>> >
> > > > >>>> >> Hi Don, Ron
> > > > >>>> >>
> > > > >>>> >> Thanks for your suggestions. I tried them but they did not work. I
> > > > >>>> >> forgot
> > > > >>>> >> to mention that the data may not always be sorted by date. I hope
> > > > >>>> >> that
> > > > >>>> >> does
> > > > >>>> >> not affect your logic?
> > > > >>>> >>
> > > > >>>> >> To give you extra info on what went wrong: the formula of Don gave
> > > > >>>> >> me an
> > > > >>>> >> extremely high number. For example: I had data for one calendar
> > > > >>>> >> year but
> > > > >>>> >> the
> > > > >>>> >> result of Don's formula was more than 366. This is not possible
> > > > >>>> >> because
> > > > >>>> >> there are only 366 unique dates in a year. The formula of Ron
> > > > >>>> >> returned
> > > > >>>> >> zero
> > > > >>>> >> as a result.
> > > > >>>> >>
> > > > >>>> >> Thanks.
> > > > >>>> >>
> > > > >>>> >> "Ron Coderre" wrote:
> > > > >>>> >>
> > > > >>>> >> > Try something like this:
> > > > >>>> >> >
> > > > >>>> >> > With this structure in A1:A16
> > > > >>>> >> > Date Dept
> > > > >>>> >> > 1/1/2007 A
> > > > >>>> >> > 1/1/2007 A
> > > > >>>> >> > 1/1/2007 A
> > > > >>>> >> > 1/1/2007 B
> > > > >>>> >> > 1/1/2007 C
> > > > >>>> >> > 1/1/2007 C
> > > > >>>> >> > 1/1/2007 C
> > > > >>>> >> > 1/15/2007 A
> > > > >>>> >> > 1/15/2007 B
> > > > >>>> >> > 1/15/2007 C
> > > > >>>> >> > 1/15/2007 C
> > > > >>>> >> > 2/1/2007 A
> > > > >>>> >> > 2/1/2007 A
> > > > >>>> >> > 2/1/2007 B
> > > > >>>> >> > 2/1/2007 B
> > > > >>>> >> >
> > > > >>>> >> > And...
> > > > >>>> >> > E1: (StartDate eg: 01/01/2007)
> > > > >>>> >> > F1: (EndDate eg: 01/31/2007)
> > > > >>>> >> > G1: (DeptName eg: A)
> > > > >>>> >> >
> > > > >>>> >> > Then this formula returns the number of unique dates in that range
> > > > >>>> >> > where the
> > > > >>>> >> > DeptName matches the name in G1:
> > > > >>>> >> > =SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDEX(A:A,E1):INDEX(A:A,F1)),(B2:B20=G1)*(A2:A20>=E1)*(A2:A20<=F1)*A2:A20,0)))
> > > > >>>> >> >
> > > > >>>> >> > Using the above example, the formula returns: 2
> > > > >>>> >> > Dept A appears at least once with 1/1/2007 and 1/15/2007
> > > > >>>> >> >
> > > > >>>> >> > Is that something you can work with?
> > > > >>>> >> > ***********
> > > > >>>> >> > Regards,
> > > > >>>> >> > Ron
> > > > >>>> >> >
> > > > >>>> >> > XL2003, WinXP
> > > > >>>> >> >
> > > > >>>> >> >
> > > > >>>> >> > "DKS" wrote:
> > > > >>>> >> >
> > > > >>>> >> > > I have a sheet, with each row containing one record. Column A
> > > > >>>> >> > > contains a
> > > > >>>> >> > > date, and column B contains a department. The contents of the
> > > > >>>> >> > > column
> > > > >>>> >> > > A
> > > > >>>> >> > > (date) are either a simple date "dd/mm/yyyy" format or a
> > > > >>>> >> > > timestamp
> > > > >>>> >> > > e.g.
> > > > >>>> >> > > "dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A,
> > > > >>>> >> > > Column B,
> > > > >>>> >> > > Column
> > > > >>>> >> > > A + Column B.
> > > > >>>> >> > >
> > > > >>>> >> > > I needed (ideally) a worksheet function that would allow me to
> > > > >>>> >> > > obtain
> > > > >>>> >> > > the
> > > > >>>> >> > > following information:
> > > > >>>> >> > >
> > > > >>>> >> > > Based on a START DATE, END DATE and DEPT NAME return to me the
> > > > >>>> >> > > number
> > > > >>>> >> > > of
> > > > >>>> >> > > times I have the DEPT NAME occuring in my data for between the
> > > > >>>> >> > > START
> > > > >>>> >> > > DATE and
> > > > >>>> >> > > END DATE (both dates included) but counting multiple rows for
> > > > >>>> >> > > the
> > > > >>>> >> > > same date
> > > > >>>> >> > > as only 1 record. Thus if I have 27 rows for the same
> > > > >>>> >> > > department
> > > > >>>> >> > > with date
> > > > >>>> >> > > July 15, then it should give me only the value 1.
> > > > >>>> >> > >
> > > > >>>> >> > > The time-part in the timestamp format can be ignored. It exists
> > > > >>>> >> > > because of
> > > > >>>> >> > > input data coming from various sources and some sources store
> > > > >>>> >> > > time
> > > > >>>> >> > > and some
> > > > >>>> >> > > do not store time.
> > > > >>>> >> > >
> > > > >>>> >> > > How could I do the above with a worksheet function?
> > > > >>>> >> > >
> > > > >>>> >> > > Many thanks.
> > > > >>>>
> > > > >>>>
> > > > >>
> > > > >
> > > > >
> > > >
> > > >
|
| |
|
| |
|
| |
|
Index ‹ Excel ‹ worksheet functions |
- Next
- 1
- Excel Programming >> How do you hide macro names from showing in "Tools"?I've written an application and I've protected both the worksheets an
the VBA code. But the macros still show in Tools, Macros, Macros...
A user can still select and run a macro from the Tools menu. I want t
control all macros from the buttons I have on the various sheets of th
workbook. Can I hide the macro names and/or prevent "Run" from bein
chosen?
Thanks in advance.
Nashu
--
Nashu
-----------------------------------------------------------------------
Nashua's Profile: http://www.highdots.com/forums/m142
View this thread: http://www.highdots.com/forums/t307899
- 2
- Excel Misc >> Excel 2002, Menu and Tool bars are gone.Hi,
Just had a call from a user that her Menu bar (File, Edit,
View, Tools, etc...) is gone from all spreadsheets.
I cannot restore them due to the menu bar being gone. How
can I replace this without using safe mode?
Any help is appreciated.
- 3
- Excel Misc >> I cannot find an error in excelI have read other answers, but no help. This is simple math, but when I put
a formula in as simples as =sum(F9+F10) or =sum(F9:F10), it is adding or
deleting a penny in my total column...help
- 4
- 5
- Excel Programming >> Import xls file..vba excel2000Hello,
How can I import the contents
of an .xls file on users harddisk
to the current worksheet via
vba?
Or better way to get the xls data
of a file to my worksheet which
holds my macro to do the work
needed.
Thanks For Your Time.
Cheers,
Paul
- 6
- Excel Programming >> Problems with an if (using rng Nothing) - only to produce sheet givenHi,
I am very glad for all the support I have received over the last
couple of days. I have now a problem. Given that there is already an
existing sheet (in this case "Global - " & ConditionSize & " Banks" ),
the code works smoothly (thus not producing a new sheet since it is
already there). But if the sheet is not there, I want the rng to still
be Nothing and thus it should produce a new sheet. But if the macro
can't find the sheet in question it says "Run Time Error '9':
Subscript out of range". I understand the problem encountered, I just
don't know how to get around it. I'd rather not use On Error Resume
Next.
Any suggestions?
Code:
'Copies the RawData sheet to a new sheet
If Sht2 = "Global - " & ConditionSize & " Banks" Then
Set rng = Nothing
Set rng = Worksheets(Sht2)
If rng Is Nothing Then
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If
Else:
Sheets(Sht).Copy After:=Sheets("Assumptions")
Sheets(Sht & " (2)").Name = Sht2
End If
Regards,
Peder
- 7
- Excel Misc >> Pivot Table Refresh SpeedHello.
I'm using Excel 2000.
I've implemented a pivot table accessing an Access 2000
database. I refresh the data in the pivot table once a
week, then email a link to my users. The problem is,
whenever I refresh the data in the pivottable, it reads
the data 10 rows at a time usually. Sometimes, it reads
70 rows at a time (at a time being about .5 seconds per
read). Other times, it's even faster. I have a lot of
data, and sometimes the update only takes a few seconds,
other times I need to go make some coffee and socialize.
Any way to speed this up? I've tried setting Excel to the
highest priority in my operating system, but it doesn't do
any good. Both files are local on my hard drive.
Thanks for the help.
-Brad
- 8
- worksheet functions >> SUMPRODUCTI am trying to create a formula to count the number of text entries in that
match two criteria. This is as far as I have been able to go. Both fields are
text.
=SUMPRODUCT('Main'!A1:A1884<>"")*('Main'!C1:C1884="STHHH")
- 9
- Excel Programming >> help with IF statementI need a cell to calculate points based on position finished example if
racer finished 1st then he gets 50 points. another racer finishes 2nd he gets
49 point and so on.
Here what Ive tried but it only lets me enter up to 7 th place. I need it to
go to 50th place at least.
=IF(C5<1,"",IF(C5<2,"50",IF(C5<3,"49",IF(C5<4,"47",IF(C5<5,"46",IF(C5<6,"45",IF(C5<7,"44",IF(C5<8,"43"))))))))
Thanks
- 10
- 11
- Excel Misc >> I need some help with some functionsI have a column of numbers, and what I want to do is return the 7 highest of
the numbers. Then, I want to be able to return the row number of each one
them. The only problem I have is, if two or more of the high numbers are the
same, it will keep returning the same the row number, instead of the next and
so on.
Any suggestions?
Please let me know. lol It's probably an easy answer, I just cant find it.
- 12
- Excel Programming >> Creating formulaPlease, help me! I am trying to program my restaurant/bar schedule so that
if I try to enter a shift for an employee who is already scheduled for a
shift elsewhere, it will alert me or disallow the entry.
Thanks so much!!!!!
- 13
- 14
- Excel Programming >> command ButtonHi, With one instance of excell open, I have a command button on
sheet1. I open another instance of excel. Id like to be able to click
the command button on workbook1 and tell workbook2 that its Range("a1")
= a certain value. Any Ideas. Regards Robert
- 15
- Excel Misc >> VBA help (and other) pleaseMe again,
A few items:
1) I have plugged this into VB to deny print unless user has selected valid
info from dropdowns:
Private Sub Workbook_BeforePrint_(Cancel As Boolean)
Cancel As Boolean
Cancel = IsEmpty(ShippingRequestForm)(W11, W13, B10, B14, B18, B23,
B37, D37, N37)
Needless to say, ain't workin' & I know nada about VB but trying! Where did
I muck this up at?
2)I have 2 cells with DV (W11 & W13). Trying to make it so user can not
bypass these without choosing from dropdowns. Formula works:
(=IF($W$13="Prepaid,Collect","okay","Invalid"). I can still tab right
through it. If I type anything other than, the error message does work
though. What do I need to add? Additional nested function? Or seperate
formula all together?
3)Dynamic DV list, @ $D$37 for "Description", want same "IsEmpty" rule also
with the ability for me to nest a list of invalid entries ("Documents",
"Docs", "Gift" etc) resulting in error message.
Thx for the email Debra. Gord, Dave et al have been above and beyond. Feel
like I've attended an XL seminar!
Hey Gord, after these my (Your!) beloved Shipping Request Form will be done.
I'll send a copy to you for Xmas! LOL
Thx
|
|
|