Ranking sales and group in 5 tier's VBA or Macro help

jlhop66

New Member
Joined
Apr 24, 2014
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
1658164570396.png


Column "C" equation example
1658164665663.png
 
Absolutely amazing, Thank you RobP and Akuini for all you help and patients. Yes the VBA code works perfectly. I ran the code with live data and PERFT!!.

Thank you, Thank you, Thank you!!!
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top