Printable View
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
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,
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?
Use the year of the first cell in the rangeQuote:
Originally Posted by firefytr
=IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),"Yes","No")
Can I use that formula in VBA?
Sure
[vba]Range("B10").Formula = " =IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),""Yes"",""No"")"[/vba]
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):
[vba]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"")"
[/vba]
HTH,
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.Quote:
Originally Posted by firefytr
Exactly. Ambiguity at its best. :)
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.
I just assumed returning a cell value that said "multiple years" vs "single year" and figured the op would adjust as necessary. :)
So did I, but you know that Zack is a bit odd!
Well, no argument there. ;)
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.Quote:
Originally Posted by Ken Puls
Sure can. Can you tell us what column your data is in, and what row it starts in?
Nevermind,
Again, assuming column A with data starting in row 2:
[vba]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[/vba]
Hmm, could you elaborate a little on that Bob? You lost me at Zack... :bug:Quote:
Originally Posted by xld
Ehrm... he said odd, not dense. ;)
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.
[vba]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[/vba]
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! :)
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
[vba]
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
[/vba]
This is the code I got out of that:
[vba]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[/vba]
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?
Just bumping it up
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.
Without testing your code myself, if you step through your code, what does "sFormula" evaluate to?
how do I check what it evaluates to? I can never get the immediate window or anything like that working.
Step through the code and after sFormula is set, type
?sFormula in the immediate window.
Thank you xld
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:
[VBA]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 & ")"[/VBA]
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)
Assuming that the worksheet is called detail, you need
[vba]
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
[/vba]
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.
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?
You can use
Activesheet.Evaluate(sFormula)
to see the result
xld when I use that in the immediate window it comes up blank: the result doesn't pop up and neither does an error
I am sorry, it is difficult to determine from afar.
Type ?sformula in the immediate window and tell us what you see.
This is what I get in the immediate window:
[VBA]SUMPRODUCT(--(YEAR(detail!B2:B7155)=YEAR(B2)))=COUNT(detail!B2:B7155)[/VBA]
I am just bumping this back up
Put that formula in a worksheet, and see what the result is.
ok I will try that