Segmenting a range of values based on value

[GuyGadois]

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
	A	B	C	D	E	F	G	H
1		$ 1,353,000			Segments
2	Vendors	Billed Amount	Assigned Segment		Segments	Goal	Goal Amount	Actual Segment Total
3	Vendor 41	$ 53,000	A		A	20%	$ 270,600	$ 303,000
4	Vendor 40	$ 52,000	A		B	20%	$ 270,600	$ 308,000
5	Vendor 39	$ 51,000	A		C	20%	$ 270,600	$ 292,000
6	Vendor 38	$ 50,000	A		D	20%	$ 270,600	$ 275,000
7	Vendor 37	$ 49,000	A		E	20%	$ 270,600	$ 175,000
8	Vendor 36	$ 48,000	A			100%	$ 1,353,000	$ 1,353,000
9	Vendor 35	$ 47,000	B
10	Vendor 34	$ 46,000	B
11	Vendor 33	$ 45,000	B
12	Vendor 32	$ 44,000	B
13	Vendor 31	$ 43,000	B
14	Vendor 30	$ 42,000	B
15	Vendor 29	$ 41,000	B
16	Vendor 28	$ 40,000	C
17	Vendor 27	$ 39,000	C
18	Vendor 26	$ 38,000	C
19	Vendor 25	$ 37,000	C
20	Vendor 24	$ 36,000	C
21	Vendor 23	$ 35,000	C
22	Vendor 22	$ 34,000	C
23	Vendor 21	$ 33,000	C
24	Vendor 20	$ 32,000	D
25	Vendor 19	$ 31,000	D
26	Vendor 18	$ 30,000	D
27	Vendor 17	$ 29,000	D
28	Vendor 16	$ 28,000	D
29	Vendor 15	$ 27,000	D
30	Vendor 14	$ 26,000	D
31	Vendor 13	$ 25,000	D
32	Vendor 12	$ 24,000	D
33	Vendor 11	$ 23,000	D
34	Vendor 10	$ 22,000	E
35	Vendor 9	$ 21,000	E
36	Vendor 8	$ 20,000	E
37	Vendor 7	$ 19,000	E
38	Vendor 6	$ 18,000	E
39	Vendor 5	$ 17,000	E
40	Vendor 4	$ 16,000	E
41	Vendor 3	$ 15,000	E
42	Vendor 2	$ 14,000	E
43	Vendor 1	$ 13,000	E
Sheet2

Cell Formulas
Range	Formula
B1	B1	=SUM(B3:B46)
G3:G7	G3	=$B$1*F3
H3:H7	H3	=SUMIF(C3:C43,E3,B3:B43)
G8:H8	G8	=SUM(G3:G7)

 

[Joe4]

Try this VBA code to populate column C:

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

 

[GuyGadois]

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

 

[Joe4]

Does it offer a "Debug" button with that error message?
If so, and you click it, which line of code does it highlight?

 

[GuyGadois]

No, just the OK button

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

segE = Cells(rE, "E")

 

[Joe4]

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?

 

[GuyGadois]

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!

 

[Joe4]

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.