PDA

View Full Version : test if more than one year



Djblois
01-10-2007, 02:01 PM
I was wondering if it is at all possible to test for multiple years in two different ways

1) If I have multiple dates in a column I would like to test if there are multiple years (ex: 2005 and 2006)
2) If I use the dates in a pivottable I would like to test the pivottable column field of date for more than one year. Same as above

Ken Puls
01-10-2007, 02:36 PM
I'm not sure on the Pivot Table portion, but for the first one, I would think a formula could do it. Say you have your dates in cells A1:A100:

=if(year(max(A1:A100))<>year(min(A1:A100)),"Multiple years","Same year")

HTH,

Zack Barresse
01-10-2007, 02:42 PM
If your data is in column A and they are all dates, you could use something like this...


=IF(SUMPRODUCT(--(YEAR(A2:A10)=2006))=COUNT(A2:A10),"Yes","No")


Of course you would need to define the year (2006 in this example) and it assumes a range of only A2:A10.

Is the year defined here, or are you looking for any difference?

Bob Phillips
01-10-2007, 02:47 PM
=IF(SUMPRODUCT(--(YEAR(A2:A10)=2006))=COUNT(A2:A10),"Yes","No")

Of course you would need to define the year (2006 in this example) and it assumes a range of only A2:A10.

Is the year defined here, or are you looking for any difference?

Use the year of the first cell in the range

=IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),"Yes","No")

Djblois
01-10-2007, 02:52 PM
Can I use that formula in VBA?

Ken Puls
01-10-2007, 02:58 PM
Sure

Range("B10").Formula = " =IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),""Yes"",""No"")"

You may want to go to FormulaR1C1 if you want to set the ranges via code. In that case it would look like (for the same thing):
Range("B10").Formula = "=IF(SUMPRODUCT(--(YEAR(R[-8]C[-1]:RC[-1])=YEAR(R[-8]C[-1])))=COUNT(R[-8]C[-1]:RC[-1]),""Yes"",""No"")"


HTH,

Zack Barresse
01-10-2007, 03:01 PM
I had thought of that Bob, but alas did not use it as the OP was (I didn't think) very specific. Of course MSFT still did not put in an ISDATE() function, which totally baffles my mind.

Bob Phillips
01-10-2007, 05:03 PM
I had thought of that Bob, but alas did not use it as the OP was (I didn't think) very specific. Of course MSFT still did not put in an ISDATE() function, which totally baffles my mind.

Aah, I see what you mean. He could have meant multiple year dates, of soecifically 2005 and 2006, rather than multiple year dates.

Zack Barresse
01-10-2007, 05:04 PM
Exactly. Ambiguity at its best. :)

Bob Phillips
01-10-2007, 05:10 PM
If it were the former, what should be done if there were 2004 and 2006? Don't answer, I am sure it doesn't matter.

Ken Puls
01-10-2007, 05:22 PM
I just assumed returning a cell value that said "multiple years" vs "single year" and figured the op would adjust as necessary. :)

Bob Phillips
01-11-2007, 02:25 AM
So did I, but you know that Zack is a bit odd!

Ken Puls
01-11-2007, 06:36 AM
Well, no argument there. ;)

Djblois
01-11-2007, 07:24 AM
Sure

Range("B10").Formula = " =IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),""Yes"",""No"")"

You may want to go to FormulaR1C1 if you want to set the ranges via code. In that case it would look like (for the same thing):
Range("B10").Formula = "=IF(SUMPRODUCT(--(YEAR(R[-8]C[-1]:RC[-1])=YEAR(R[-8]C[-1])))=COUNT(R[-8]C[-1]:RC[-1]),""Yes"",""No"")"


HTH,

I like this it seems it will do what I am looking for. However is it possible, to have adjust to the size of the column. Since the column will always be different lengths.

Ken Puls
01-11-2007, 07:45 AM
Sure can. Can you tell us what column your data is in, and what row it starts in?

Ken Puls
01-11-2007, 07:50 AM
Nevermind,

Again, assuming column A with data starting in row 2:

With ActiveSheet
.Range("B10").Formula = _
"=IF(SUMPRODUCT(--(YEAR(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & _
")=YEAR(A2)))=COUNT(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & "),""Yes"",""No"")"
End With

Zack Barresse
01-11-2007, 10:16 AM
So did I, but you know that Zack is a bit odd!
Hmm, could you elaborate a little on that Bob? You lost me at Zack... :bug:

Ken Puls
01-11-2007, 10:19 AM
Ehrm... he said odd, not dense. ;)

Djblois
01-17-2007, 11:34 AM
Would this work as a VBA statement? I am trying to use it to say if more than 1 year then group the date by year. The rest I already have, I just need to figure out this last piece of the puzzle.

IF(SUMPRODUCT(--(YEAR(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & ")=YEAR(A2)))
_ =COUNT(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row &") > 1 then

Zack Barresse
01-17-2007, 12:09 PM
It should, as long as you have it in a With statement that contains the sheet, as that is how you have it referenced. The logic is sufficient though. I know a good way to find out... test it! :)

Bob Phillips
01-18-2007, 05:05 AM
No it won't!

SUMPRODUCT is a worksheet function, not a VBA command. Worse still, it cannot even be called from the WorksheetFunction object/property.

You need to evaluate it



Dim sFormula As String
sFormula = "SUMPRODUCT(--(YEAR(A2:A" & _
.Cells(.Rows.Count, 1).End(xlUp).Row & _
")=YEAR(A2)))=COUNT(A2:A" & _
.Cells(.Rows.Count, 1).End(xlUp).Row & ")"

If ActiveSheet.Evaluate(sFormula) > 1 Then

Djblois
01-18-2007, 02:17 PM
This is the code I got out of that:

Dim pf As PivotField
sFormula = "SUMPRODUCT(--(YEAR(B2:B" & Cells(Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(B2:B" & Cells(Rows.Count, 2).End(xlUp).Row & ")"

Set pf = pt.PivotFields("Date")
If detail.Evaluate(sFormula) = 1 Then
pf.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False _
, False, True, False, False)
If PivotTableOptions.Descending.Value = True Then
pt.PivotFields("Date").AutoSort xlDescending, "Date"
End If
End If
If detail.Evaluate(sFormula) > 1 Then
pf.LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False _
, False, True, False, True)
If PivotTableOptions.Descending.Value = True Then
pt.PivotFields("Date").AutoSort xlDescending, "Date"
pt.PivotFields("Years").AutoSort xlDescending, "Years"
End If
End If

I am trying to group the pivottable data field of date by year if more than one year but don't group it by year if it is only one year. Detail is the name of the sheet with data. Can I get that out of this code?

Djblois
01-19-2007, 04:41 AM
Just bumping it up

Djblois
01-19-2007, 11:35 AM
Does anyone know the problem? I have tried many different ways of fixing it. It might be something small that I never thought of or it may be large.

Zack Barresse
01-19-2007, 01:49 PM
Without testing your code myself, if you step through your code, what does "sFormula" evaluate to?

Djblois
01-19-2007, 02:12 PM
how do I check what it evaluates to? I can never get the immediate window or anything like that working.

Bob Phillips
01-19-2007, 02:15 PM
Step through the code and after sFormula is set, type

?sFormula in the immediate window.

Djblois
01-19-2007, 02:45 PM
Thank you xld

Djblois
02-10-2007, 10:55 AM
I stepped through the code and it is giving me a type mismatch error on the line that evaluates it. I had to change the formula a little:

sFormula = "SUMPRODUCT(--(YEAR(detail.B2:B" & Cells(detail.Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(detail.B2:B" & Cells(detail.Rows.Count, 2).End(xlUp).Row & ")"

Since at the time that it evaluates the formula, I am on another worksheet than the one I needed to evaluate I added the name of the worksheet (I mean I set the worksheet = detail)

Bob Phillips
02-10-2007, 12:18 PM
Assuming that the worksheet is called detail, you need



Dim sFormula
With Worksheets("detail")
sFormula = "SUMPRODUCT(--(YEAR(detail!B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row & _
")=YEAR(B2)))=COUNT(detail!B2:B" & .Cells(.Rows.Count, 2).End(xlUp).Row & ")"
End With

Djblois
02-12-2007, 10:21 AM
XLD,

Would that work if I don't know the name of the tab? I set that sheet = detail but the name may be something different.

Djblois
02-12-2007, 12:02 PM
Another problem is when I step through the code and use the immediate window after the formula is evaluated the sFormula is equal to the formula not the answer. Hence it isn't working?

Bob Phillips
02-12-2007, 12:30 PM
You can use

Activesheet.Evaluate(sFormula)

to see the result

Djblois
02-12-2007, 12:48 PM
xld when I use that in the immediate window it comes up blank: the result doesn't pop up and neither does an error

Bob Phillips
02-12-2007, 12:57 PM
I am sorry, it is difficult to determine from afar.

Type ?sformula in the immediate window and tell us what you see.

Djblois
02-12-2007, 01:12 PM
This is what I get in the immediate window:

SUMPRODUCT(--(YEAR(detail!B2:B7155)=YEAR(B2)))=COUNT(detail!B2:B7155)

Djblois
02-13-2007, 09:52 AM
I am just bumping this back up

Bob Phillips
02-13-2007, 09:58 AM
Put that formula in a worksheet, and see what the result is.

Djblois
02-13-2007, 12:03 PM
ok I will try that