Counting Array element

[asingh]

Hi There,

i have two columns, strategy, month and third is desired output

STRATEGY month desired output
cta jan 2
cta jan 2
cta feb 1
short feb 1
credit mar 1
long mar 1
event apr 2
event apr 2
short may 1
fixed may 1
long may 1
event june 1


i want to write a macro that groups strategy(column 1) by month(column2) abd
prints in desired output(column 3) . is this poosible as tried to do it by
comparing array , but didnt scucced

 

[Steve Dunn]

Does it need to be a macro?

=SUMPRODUCT(--($A$2:$A$13&$B$2:$B$13=$A2&$B2))



wi

 

[asingh]

Does it need to be a macro?

=SUMPRODUCT(--($A$2:$A$13&$B$2:$B$13=$A2&$B2))

wi

Hi There,

[quoted text clipped - 18 lines]

prints in desired output(column 3) . is this poosible as tried to do it by
comparing array , but didnt scucced

reason why i want a macro because this would drive other part of models ,
that is why i want it to be a macro. otherwise what you have done works
perfectly fine

 

[Dave Peterson]

Have you thought about having the macro plop the formula into column C -- and
the macro could even convert to values if you wanted.

I like this alternative formula:
=sumproduct(--(A2:A13=A2),--(B2:B13=B2))

Option Explicit
Sub testme()
Dim myRng As Range
Dim myFormula As String
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("A2:A" & LastRow)

'trying for a formula that looks like:
'=sumproduct(--(A2:A13=A2),--(B2:B13=B2))
myFormula _
= "=sumproduct(" _
& "--(" & myRng.Address(0, 0) _
& "=" & myRng.Cells(1).Address(0, 0) & ")," _
& "--(" & myRng.Offset(0, 1).Address(0, 0) _
& "=" & myRng.Cells(1).Offset(0, 1).Address(0, 0) & "))"
End With

With myRng.Offset(0, 2)
.Formula = myFormula
'and if you don't want the formulas
.Value = .Value
End With

End Sub

=======
Just some info about that formula...

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Does it need to be a macro?

=SUMPRODUCT(--($A$2:$A$13&$B$2:$B$13=$A2&$B2))

wi

Hi There,

[quoted text clipped - 18 lines]

prints in desired output(column 3) . is this poosible as tried to do it by
comparing array , but didnt scucced

reason why i want a macro because this would drive other part of models ,
that is why i want it to be a macro. otherwise what you have done works
perfectly fine

 

[asingh]

thanks dave and steve, for your help.