Segmenting a range of values based on value

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
We would like to automate how we segment our vendors. Below is a sample list. An upload will provide the data in columns A and B and we would like the value in C to be auto assigned with either a macro or a formula (not sure which to use). The segment names and parameters are listed in columns E and F. For this example Segment A will be the first 20% of the Total Billed (B1) amount which is $270,000 ((G3). We would like to round up meaning that it takes the last Vendor after that pushes the value over $270,600 in the example. A segment actually shows a total Billed Amount of $303,000 and that is ok. The goal is to rank all vendors by Billed amount with A's being the top 20% of revenue, B's the next 20%, C's the next 20% and so on. The Goal % (column F) is a variable number and could be 30,25,20,15,10 some times.

Is this possible? Any help is appreciated.

GG


Segmenting Generator.xlsx
ABCDEFGH
1$ 1,353,000Segments
2Vendors Billed Amount Assigned SegmentSegmentsGoalGoal AmountActual Segment Total
3Vendor 41$ 53,000AA20%$ 270,600$ 303,000
4Vendor 40$ 52,000AB20%$ 270,600$ 308,000
5Vendor 39$ 51,000AC20%$ 270,600$ 292,000
6Vendor 38$ 50,000AD20%$ 270,600$ 275,000
7Vendor 37$ 49,000AE20%$ 270,600$ 175,000
8Vendor 36$ 48,000A100%$ 1,353,000$ 1,353,000
9Vendor 35$ 47,000B
10Vendor 34$ 46,000B
11Vendor 33$ 45,000B
12Vendor 32$ 44,000B
13Vendor 31$ 43,000B
14Vendor 30$ 42,000B
15Vendor 29$ 41,000B
16Vendor 28$ 40,000C
17Vendor 27$ 39,000C
18Vendor 26$ 38,000C
19Vendor 25$ 37,000C
20Vendor 24$ 36,000C
21Vendor 23$ 35,000C
22Vendor 22$ 34,000C
23Vendor 21$ 33,000C
24Vendor 20$ 32,000D
25Vendor 19$ 31,000D
26Vendor 18$ 30,000D
27Vendor 17$ 29,000D
28Vendor 16$ 28,000D
29Vendor 15$ 27,000D
30Vendor 14$ 26,000D
31Vendor 13$ 25,000D
32Vendor 12$ 24,000D
33Vendor 11$ 23,000D
34Vendor 10$ 22,000E
35Vendor 9$ 21,000E
36Vendor 8$ 20,000E
37Vendor 7$ 19,000E
38Vendor 6$ 18,000E
39Vendor 5$ 17,000E
40Vendor 4$ 16,000E
41Vendor 3$ 15,000E
42Vendor 2$ 14,000E
43Vendor 1$ 13,000E
Sheet2
Cell Formulas
RangeFormula
B1B1=SUM(B3:B46)
G3:G7G3=$B$1*F3
H3:H7H3=SUMIF(C3:C43,E3,B3:B43)
G8:H8G8=SUM(G3:G7)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this VBA code to populate column C:
VBA Code:
Sub MySegmenter()

    Dim lrB As Long
    Dim lrE As Long
    Dim rB As Long
    Dim rE As Long
    Dim goalE As Double
    Dim sumB As Double
    Dim segE As String
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
'   Find last row with data in column E
    lrE = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Initialize starting row for B
    rB = 3
    
'   Loop through col E
    For rE = 3 To lrE
'       Initialize sumB
        sumB = 0
'       Get goal amount
        goalE = Cells(rE, "G")
'       Get segment code
        segE = Cells(rE, "E")
'       Loop through rows in column B
        Do
'           Add value to running sum
            sumB = sumB + Cells(rB, "B")
'           Write segment code to column C
            Cells(rB, "C") = segE
'           Increment row B counter
            rB = rB + 1
'           If running sum meets or exceeds goal or past last line...
            If (sumB >= goalE) Or (rB > lrB) Then
'               Reset running sum
                sumB = 0
'               Exit loop
                Exit Do
            End If
        Loop
    Next rE
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
Joe, thanks for your reply. I get a RUn-time error "13", Type Mismatch but the error gives no clue what's triggering that error. Any clue why?

GG
 
Upvote 0
Does it offer a "Debug" button with that error message?
If so, and you click it, which line of code does it highlight?
 
Upvote 0
No, just the OK button

I inserted a few steps and It seems to show the error at this line

segE = Cells(rE, "E")
 
Upvote 0
If you go into your VBA code, and from the "Debug" menu, select "Compile VBAProject", what happens?
Does it return any errors and/or highlight any lines of VBA code?
 
Upvote 0
I discovered the issue. I had added a column while I was trying to solve the problem so it didn't exactly match the initial sheet I posted. Thank you for your help! HPN!
 
Upvote 0
I discovered the issue. I had added a column while I was trying to solve the problem so it didn't exactly match the initial sheet I posted. Thank you for your help! HPN!
I figured it might be something like that!
My next step was to ask you to share your workbook.

Glad you got it figured out.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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