Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting Array element

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default Counting Array element

Does it need to be a macro?

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



wi
"asingh" <u59311@uwe wrote in message news:a66bdd1e0c55a@uwe...
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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting Array element

Steve Dunn wrote:
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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Counting Array element

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

asingh wrote:

Steve Dunn wrote:
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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Counting Array element

thanks dave and steve, for your help.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can one store a string in a Array element? [email protected] Excel Discussion (Misc queries) 1 April 17th 07 12:53 PM
How to "return" the array element number in VBA EagleOne Excel Discussion (Misc queries) 4 December 12th 06 11:30 PM
Search array and return element No Ron Excel Worksheet Functions 7 May 17th 06 05:27 AM
array functions and complex element values frustrated Excel Worksheet Functions 0 March 14th 06 12:44 AM
Permutations of an array element < to a value Bruce Excel Worksheet Functions 3 January 31st 06 05:00 PM


All times are GMT +1. The time now is 10:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"