Hello Group!!! I am working with a very large data set of 200+ items across 3,000+ stores. I finally figured out the equation to rank stores by sales velocity, and an equation to put the stores into 5 groups of stores for analysis. right now I am doing this manually and it takes me 7 to 9 hours each week, this is killing me.
my challenge is creating a VBA program to run my equations for the length of store numbers that vary from 600 to 3,00 stores and start over at the next item number in the list. I need to make this program to loop over and over until it reached the end of the item number list witch is in column "A".
Rank store sales: 1 through 600 or 3,000 (store count vary) (Column "K" is my sales qty)
This data is in Column "B" - FYI, I can put this data at the end of my data table if that makes it easier.
=RANK.EQ(K10,$K$10:$K$2598,0)+COUNTIF($K10:K10,K10)-1
This data is in Column "C"
Grouping of ranked stores - FYI, I can also put this data at the end of my data table if that makes it easier.
=MAX( ROUNDUP( PERCENTRANK($B$10:$B$2929, B10) *$I$1, 0),1)
Here is my attempt to write the VBA code - but it's not work. any help is greatly appreciated.
Sub aTest()
Dim LR As Long, rCell As Range, strAdd As String
LR = Cells(Rows.Count, "K").End(xlUp).Row
With Range("K4:K" & LR)
.Formula = "=RANK.EQ(K4,$K$4:$K$2592,0)+COUNTIF($K4:K4,K4)-1))"
.NumberFormat = "0%"
End With
For Each rCell In Range("B4:B" & LR)
If rCell <> "" Then
strAdd = rCell.Address
Else
rCell.Formula = MAX( ROUNDUP( PERCENTRANK($B$4:$B$2923, B4) *$I$1, 0),1)
End If
Next rCell
End Sub
Column "B" equation example
Column "C" equation example
my challenge is creating a VBA program to run my equations for the length of store numbers that vary from 600 to 3,00 stores and start over at the next item number in the list. I need to make this program to loop over and over until it reached the end of the item number list witch is in column "A".
Rank store sales: 1 through 600 or 3,000 (store count vary) (Column "K" is my sales qty)
This data is in Column "B" - FYI, I can put this data at the end of my data table if that makes it easier.
=RANK.EQ(K10,$K$10:$K$2598,0)+COUNTIF($K10:K10,K10)-1
This data is in Column "C"
Grouping of ranked stores - FYI, I can also put this data at the end of my data table if that makes it easier.
=MAX( ROUNDUP( PERCENTRANK($B$10:$B$2929, B10) *$I$1, 0),1)
Here is my attempt to write the VBA code - but it's not work. any help is greatly appreciated.
Sub aTest()
Dim LR As Long, rCell As Range, strAdd As String
LR = Cells(Rows.Count, "K").End(xlUp).Row
With Range("K4:K" & LR)
.Formula = "=RANK.EQ(K4,$K$4:$K$2592,0)+COUNTIF($K4:K4,K4)-1))"
.NumberFormat = "0%"
End With
For Each rCell In Range("B4:B" & LR)
If rCell <> "" Then
strAdd = rCell.Address
Else
rCell.Formula = MAX( ROUNDUP( PERCENTRANK($B$4:$B$2923, B4) *$I$1, 0),1)
End If
Next rCell
End Sub
Column "B" equation example
Column "C" equation example
