How to pull data from 4 different groups while staying under a set value

**alowishous** · 11-08-2013, 04:50 PM

I'm a self taught excel user and I'm struggling to find any information to help me with a formula that I'm not sure is even possible. I have a bunch of numbers already calculated throughout my spreadsheet and they have prices assigned to them. There are 4 different groups of numbers, I have to use a certain number of values from each group. I'm trying to figure out a formula that determines the highest number value while staying under a specific total price. As follows is an example of the types of numbers and situation i'm dealing with.

Group 1 Group 2 Group 3 Group 4
A B D E G H J K
1 Price Value Price Value Price Value Price Value
2 $50 22 $85 25 $69 26 $87 28
3 $58 19 $60 18 $45 16 $51 25
4 $66 21 $56 16 $62 24 $69 17
5 $82 25 $51 12 $65 23 $65 14
6 $102 30 $80 19 $78 24 $68 11
7 $81 23 $66 11 $81 24 $56 28
8 $90 19 $83 23
9 $51 10 $53 14
10 $66 13 $45 12
11 $84 15 $62 16
12 $63 11 $88 23
13 $60 9 $70 18

So that is basically what I'm working with. I need to use 1 from group 1, 2 from group 2, 2 from group 3, and 1 from group 4. The total price needs to stay under $600 as well. Once again I'm not sure if this is possible, but I appreciate any help anyone can give me. Thanks in advance.

**alowishous** · 11-08-2013, 04:52 PM

Well that didn't post how I thought it would. The groups and numbers are suppose to spread out and look like and excel spreadsheet.

**AlKey** · 11-08-2013, 04:56 PM

Hi alowishous,

I suggest you upload a spreadsheet instead pasting.

Click on go "Go Advanced" on the bottom of your screen the click on a paperclip icon and attached file.

**alowishous** · 11-08-2013, 05:14 PM

Example.xlsx

Hopefully this worked

**bmxfreedom** · 11-08-2013, 06:12 PM

The cases where you only need 1 value from group 1 and group 4 are easy because you can just use

Formula:

=MAX(B:B)

and

Formula:

=MAX(K:K)

to find the largest values in the Value columns, then

Formula:

=INDIRECT("A"&MATCH(MAX(B:B),B:B,0))

AND

Formula:

=INDIRECT("J"&MATCH(MAX(K:K),K:K,0))

to return the resulting values from the respective price columns. But because you have multiple instances of the values that are largest or 2nd largest in Groups 2 and 3, a formula will be very difficult. The following formula will give you the 2nd lowest value in column E: (NEVER MIND, that formula doesn't work either...) Finding the lowest Price value associated with this Value number will be even more tricky, or next to impossible, using formulas if the number of instances of this value are ever more than 7. (It could only be done with nested if formulas, of which, you can only have 7.)

**bmxfreedom** · 11-08-2013, 06:17 PM

Your eventual solution will likely be based in VBA. This would be a lot easier if the Price columns were sorted by value... I'm guessing that these amounts are entered manually. Code could be placed in the worksheet change event to sort the price/value columns by price automatically every time a price is entered. Then the formulas could rely on the fact that the prices exist in numerical order, so the 1st match of the largest value would return the smallest resulting price. (I assume that is the goal; attempting to use 2 values in Group 2 and 3, but keeping the price under $600 or as low as possible?)

**alowishous** · 11-08-2013, 06:48 PM

Yeah basically, these aren't the exact values i'm using, i'm just trying to figure out if it is possible. I want the formula to use x amount of values from each group to create the highest number while staying under a certain total price y. So this example needs to find the highest total number using 1 from group 1, 2 from 2, 2 from 3, and 1 from 4 while staying under the price 600. The prices used from each group doesn't matter, it just needs to find the highest number while staying under the set price. If that doesn't make sense I can try to explain it further. Thanks for your help bmxfreedom

**bmxfreedom** · 11-08-2013, 07:26 PM

Yeah, in this case, I think some code is in order... How do you intend to define the "x" values from each group and the "y" total value top-out? Are these going to be numbers a user would enter into a cell?

**bmxfreedom** · 11-09-2013, 01:19 AM

Well, it's some super shoddy code, but it kind of works... Maybe somebody with a better understanding that I can come up with a better solution.

Function MaxAddress(The_Range)
MaxNum = Application.Max(The_Range)
For Each cell In The_Range
   If cell = MaxNum Then
      MaxAddress = cell.Address
      Exit For
   End If
Next cell
End Function
 
Sub Test()
'On Error GoTo ErrHndlr
'Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

G1LRow = WorksheetFunction.CountA(Range("A:A"))
G2LRow = WorksheetFunction.CountA(Range("D:D"))
G3LRow = WorksheetFunction.CountA(Range("G:G"))
G4LRow = WorksheetFunction.CountA(Range("J:J"))
MaxGRow = WorksheetFunction.Max(G1LRow, G2LRow, G3LRow, G4LRow)

Clr1LRow = WorksheetFunction.CountA(Range("N:N"))
Clr2LRow = WorksheetFunction.CountA(Range("O:O"))
Clr3LRow = WorksheetFunction.CountA(Range("P:P"))
Clr4LRow = WorksheetFunction.CountA(Range("Q:Q"))
MaxClrRow = WorksheetFunction.Max(Clr1LRow, Clr2LRow, Clr3LRow, Clr4LRow)
Range("N4:Q" & MaxClrRow).ClearContents
Range("M6:M" & MaxClrRow).ClearContents


'Sort Group 1
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:A" & G1LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B3:B" & G1LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:B" & G1LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Sort Group 2
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D3:D" & G2LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E3:E" & G2LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("D2:E" & G2LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Sort Group 3
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G3:G" & G3LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H3:H" & G3LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("G2:H" & G3LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Sort Group 4
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J3:J" & G4LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K3:K" & G4LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("J2:K" & G4LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
G1ValueMax = Range("B" & G1LRow).Value
G2ValueMax = Range("E" & G2LRow).Value
G3ValueMax = Range("H" & G3LRow).Value
G4ValueMax = Range("K" & G4LRow).Value
G1PriceMax = Range("A" & G1LRow).Value
G2PriceMax = Range("D" & G2LRow).Value
G3PriceMax = Range("G" & G3LRow).Value
G4PriceMax = Range("J" & G4LRow).Value

If Range("N3").Value = 0 Then
Range("N4").ClearContents
Else
Range("N4").Value = G1ValueMax
End If
If Range("O3").Value = 0 Then
Range("O4").ClearContents
Else
Range("O4").Value = G2ValueMax
End If
If Range("P3").Value = 0 Then
Range("P4").ClearContents
Else
Range("P4").Value = G3ValueMax
End If
If Range("Q3").Value = 0 Then
Range("Q4").ClearContents
Else
Range("Q4").Value = G4ValueMax
End If

If Range("N3").Value = 0 Then
Range("N5").ClearContents
Else
Range("N5").Value = G1PriceMax
End If
If Range("O3").Value = 0 Then
Range("O5").ClearContents
Else
Range("O5").Value = G2PriceMax
End If
If Range("P3").Value = 0 Then
Range("P5").ClearContents
Else
Range("P5").Value = G3PriceMax
End If
If Range("Q3").Value = 0 Then
Range("Q5").ClearContents
Else
Range("Q5").Value = G4PriceMax
End If



For1:
        If Range("N3").Value <= 1 Then
        GoTo For2
        End If
        For i = 2 To Range("N3").Value
        Range("N" & (i * 2) + 2).Value = Range("B" & G1LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("N" & (i * 2) + 3).Value = Range("A" & G1LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For2:
        If Range("O3").Value <= 1 Then
        GoTo For3
        End If
        For i = 2 To Range("O3").Value
        Range("O" & (i * 2) + 2).Value = Range("E" & G2LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("O" & (i * 2) + 3).Value = Range("D" & G2LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For3:
        If Range("P3").Value <= 1 Then
        GoTo For4
        End If
        For i = 2 To Range("P3").Value
        Range("P" & (i * 2) + 2).Value = Range("H" & G3LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("P" & (i * 2) + 3).Value = Range("G" & G3LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For4:
        If Range("Q3").Value <= 1 Then
        GoTo ForEnd
        End If
        For i = 2 To Range("Q3").Value
        Range("Q" & (i * 2) + 2).Value = Range("K" & G4LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("Q" & (i * 2) + 3).Value = Range("J" & G4LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
ForEnd:

For t = 3 To MaxGRow
Calculate

If Range("R5").Value < Range("M3").Value Then
'Everything's good.  End this.
GoTo ErrHndlr
Else
'back up 1 on the biggest $amount in the bottom of the ranges
Rg1 = WorksheetFunction.CountA(Range("N6:N" & MaxGRow * 2))
Rg2 = WorksheetFunction.CountA(Range("O6:O" & MaxGRow * 2))
Rg3 = WorksheetFunction.CountA(Range("P6:P" & MaxGRow * 2))
Rg4 = WorksheetFunction.CountA(Range("Q6:Q" & MaxGRow * 2))
MxRg = WorksheetFunction.Max(Rg1, Rg2, Rg3, Rg4)

KillTheBigGuns:
If MxRg = 0 Then
    Calculate
    If Range("R5").Value < Range("M3").Value Then
    'Everything's good.  End this.
    GoTo ErrHndlr
    End If
ChngAdrs2 = MaxAddress(Range("N5:Q5"))
ChngAdrs1 = Cells(Range(ChngAdrs2).Row - 1, Range(ChngAdrs2).Column).Address
MaxCol = ((Range(ChngAdrs2).Column - 13) * 3) - 1
ChkAdrs = Cells(1, MaxCol).Address & ":" & Cells(MaxGRow, MaxCol).Address
NextPos = Range(ChkAdrs).Rows.Count + 1
    If NextPos - 2 - (t - 3) < 3 Then
    MsgBox "You will need to change either your number of selections or your dollar amounts.  The total of the smallest values available at these quantities represent a total price over your limit of $" & Range("M3").Value
    GoTo ErrHndlr
    End If
BackupOne1 = Range(Cells(NextPos - 2 - (t - 3), MaxCol).Address).Value
BackupOne2 = Range(Cells(NextPos - 2 - (t - 3), MaxCol - 1).Address).Value
MsgBox BackupOne1
MsgBox BackupOne2

    If BackupOne1 = 0 Or BackupOne2 = 0 Then
    MsgBox "You will need to change either your number of selections or your dollar amounts.  The total of the smallest values available at these quantities represent a total price over your limit of $" & Range("M3").Value
    GoTo ErrHndlr
    End If
Range(ChngAdrs2).Value = BackupOne2
Range(ChngAdrs1).Value = BackupOne1
    If KillBigGuns = "yes" Then
    GoTo KillTheBigGuns
    Else
    GoTo Nxtt
    End If
Else
Rg1 = WorksheetFunction.CountA(Range("N6:N" & MaxGRow * 2))
Rg2 = WorksheetFunction.CountA(Range("O6:O" & MaxGRow * 2))
Rg3 = WorksheetFunction.CountA(Range("P6:P" & MaxGRow * 2))
Rg4 = WorksheetFunction.CountA(Range("Q6:Q" & MaxGRow * 2))
MxRg = WorksheetFunction.Max(Rg1, Rg2, Rg3, Rg4)
ChngAdrs2 = MaxAddress(Range("N6:Q" & MxRg + 6))
ChngAdrs1 = Cells(Range(ChngAdrs2).Row - 1, Range(ChngAdrs2).Column).Address
MaxPos = ((Range(MaxAddress(Range("N6:Q" & MxRg + 6))).Row - 3) / 2) - 1
MaxCol = ((Range(MaxAddress(Range("N6:Q" & MxRg + 6))).Column - 13) * 3) - 1
ChkAdrs = Cells(1, MaxCol).Address & ":" & Cells(MaxGRow, MaxCol).Address
    If Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3) < 3 Then
    MxRg = 0
    KillBigGuns = "yes"
    GoTo KillTheBigGuns
    End If
    
BackupOne1 = Range(Cells(Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3), MaxCol).Address).Value
BackupOne2 = Range(Cells(Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3), MaxCol - 1).Address).Value
Range(ChngAdrs2).Value = BackupOne2
Range(ChngAdrs1).Value = BackupOne1
End If
End If
Nxtt:
Next t

ErrHndlr:
Range("M5") = "Max Price"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub

End Sub

This looks at the max $ as input value in cell M3, and a formula that calculates the sum of the total price in cell R5. I've attached the version of your workbook containing the formula and the code. Try it out. I hope you can make it work.

**bmxfreedom** · 11-09-2013, 01:26 AM

I left a few things undone in the code. You might want to update it to the following:

Sub Test()
On Error GoTo ErrHndlr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

G1LRow = WorksheetFunction.CountA(Range("A:A"))
G2LRow = WorksheetFunction.CountA(Range("D:D"))
G3LRow = WorksheetFunction.CountA(Range("G:G"))
G4LRow = WorksheetFunction.CountA(Range("J:J"))
MaxGRow = WorksheetFunction.Max(G1LRow, G2LRow, G3LRow, G4LRow)

Clr1LRow = WorksheetFunction.CountA(Range("N:N"))
Clr2LRow = WorksheetFunction.CountA(Range("O:O"))
Clr3LRow = WorksheetFunction.CountA(Range("P:P"))
Clr4LRow = WorksheetFunction.CountA(Range("Q:Q"))
MaxClrRow = WorksheetFunction.Max(Clr1LRow, Clr2LRow, Clr3LRow, Clr4LRow)
Range("N4:Q" & MaxClrRow).ClearContents
Range("M6:M" & MaxClrRow).ClearContents


'Sort Group 1
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:A" & G1LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B3:B" & G1LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:B" & G1LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Sort Group 2
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D3:D" & G2LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E3:E" & G2LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("D2:E" & G2LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Sort Group 3
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G3:G" & G3LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H3:H" & G3LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("G2:H" & G3LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Sort Group 4
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J3:J" & G4LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K3:K" & G4LRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("J2:K" & G4LRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
G1ValueMax = Range("B" & G1LRow).Value
G2ValueMax = Range("E" & G2LRow).Value
G3ValueMax = Range("H" & G3LRow).Value
G4ValueMax = Range("K" & G4LRow).Value
G1PriceMax = Range("A" & G1LRow).Value
G2PriceMax = Range("D" & G2LRow).Value
G3PriceMax = Range("G" & G3LRow).Value
G4PriceMax = Range("J" & G4LRow).Value

If Range("N3").Value = 0 Then
Range("N4").ClearContents
Else
Range("N4").Value = G1ValueMax
End If
If Range("O3").Value = 0 Then
Range("O4").ClearContents
Else
Range("O4").Value = G2ValueMax
End If
If Range("P3").Value = 0 Then
Range("P4").ClearContents
Else
Range("P4").Value = G3ValueMax
End If
If Range("Q3").Value = 0 Then
Range("Q4").ClearContents
Else
Range("Q4").Value = G4ValueMax
End If

If Range("N3").Value = 0 Then
Range("N5").ClearContents
Else
Range("N5").Value = G1PriceMax
End If
If Range("O3").Value = 0 Then
Range("O5").ClearContents
Else
Range("O5").Value = G2PriceMax
End If
If Range("P3").Value = 0 Then
Range("P5").ClearContents
Else
Range("P5").Value = G3PriceMax
End If
If Range("Q3").Value = 0 Then
Range("Q5").ClearContents
Else
Range("Q5").Value = G4PriceMax
End If



For1:
        If Range("N3").Value <= 1 Then
        GoTo For2
        End If
        For i = 2 To Range("N3").Value
        Range("N" & (i * 2) + 2).Value = Range("B" & G1LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("N" & (i * 2) + 3).Value = Range("A" & G1LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For2:
        If Range("O3").Value <= 1 Then
        GoTo For3
        End If
        For i = 2 To Range("O3").Value
        Range("O" & (i * 2) + 2).Value = Range("E" & G2LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("O" & (i * 2) + 3).Value = Range("D" & G2LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For3:
        If Range("P3").Value <= 1 Then
        GoTo For4
        End If
        For i = 2 To Range("P3").Value
        Range("P" & (i * 2) + 2).Value = Range("H" & G3LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("P" & (i * 2) + 3).Value = Range("G" & G3LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
For4:
        If Range("Q3").Value <= 1 Then
        GoTo ForEnd
        End If
        For i = 2 To Range("Q3").Value
        Range("Q" & (i * 2) + 2).Value = Range("K" & G4LRow - i + 1).Value
        Range("M" & (i * 2) + 2).Value = "Next Value"
        Range("Q" & (i * 2) + 3).Value = Range("J" & G4LRow - i + 1).Value
        Range("M" & (i * 2) + 3).Value = "Next Price"
        Next i
ForEnd:

For t = 3 To MaxGRow
Calculate

If Range("R5").Value < Range("M3").Value Then
'Everything's good.  End this.
GoTo ErrHndlr
Else
'back up 1 on the biggest $amount in the bottom of the ranges
Rg1 = WorksheetFunction.CountA(Range("N6:N" & MaxGRow * 2))
Rg2 = WorksheetFunction.CountA(Range("O6:O" & MaxGRow * 2))
Rg3 = WorksheetFunction.CountA(Range("P6:P" & MaxGRow * 2))
Rg4 = WorksheetFunction.CountA(Range("Q6:Q" & MaxGRow * 2))
MxRg = WorksheetFunction.Max(Rg1, Rg2, Rg3, Rg4)

KillTheBigGuns:
If MxRg = 0 Then
    Calculate
    If Range("R5").Value < Range("M3").Value Then
    'Everything's good.  End this.
    GoTo ErrHndlr
    End If
ChngAdrs2 = MaxAddress(Range("N5:Q5"))
ChngAdrs1 = Cells(Range(ChngAdrs2).Row - 1, Range(ChngAdrs2).Column).Address
MaxCol = ((Range(ChngAdrs2).Column - 13) * 3) - 1
ChkAdrs = Cells(1, MaxCol).Address & ":" & Cells(MaxGRow, MaxCol).Address
NextPos = Range(ChkAdrs).Rows.Count + 1
    If NextPos - 2 - (t - 3) < 3 Then
    MsgBox "You will need to change either your number of selections or your dollar amounts.  The total of the smallest values available at these quantities represent a total price over your limit of $" & Range("M3").Value
    GoTo ErrHndlr
    End If
BackupOne1 = Range(Cells(NextPos - 2 - (t - 3), MaxCol).Address).Value
BackupOne2 = Range(Cells(NextPos - 2 - (t - 3), MaxCol - 1).Address).Value

    If BackupOne1 = 0 Or BackupOne2 = 0 Then
    MsgBox "You will need to change either your number of selections or your dollar amounts.  The total of the smallest values available at these quantities represent a total price over your limit of $" & Range("M3").Value
    GoTo ErrHndlr
    End If
Range(ChngAdrs2).Value = BackupOne2
Range(ChngAdrs1).Value = BackupOne1
    If KillBigGuns = "yes" Then
    GoTo KillTheBigGuns
    Else
    GoTo Nxtt
    End If
Else
Rg1 = WorksheetFunction.CountA(Range("N6:N" & MaxGRow * 2))
Rg2 = WorksheetFunction.CountA(Range("O6:O" & MaxGRow * 2))
Rg3 = WorksheetFunction.CountA(Range("P6:P" & MaxGRow * 2))
Rg4 = WorksheetFunction.CountA(Range("Q6:Q" & MaxGRow * 2))
MxRg = WorksheetFunction.Max(Rg1, Rg2, Rg3, Rg4)
ChngAdrs2 = MaxAddress(Range("N6:Q" & MxRg + 6))
ChngAdrs1 = Cells(Range(ChngAdrs2).Row - 1, Range(ChngAdrs2).Column).Address
MaxPos = ((Range(MaxAddress(Range("N6:Q" & MxRg + 6))).Row - 3) / 2) - 1
MaxCol = ((Range(MaxAddress(Range("N6:Q" & MxRg + 6))).Column - 13) * 3) - 1
ChkAdrs = Cells(1, MaxCol).Address & ":" & Cells(MaxGRow, MaxCol).Address
    If Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3) < 3 Then
    MxRg = 0
    KillBigGuns = "yes"
    GoTo KillTheBigGuns
    End If
    
BackupOne1 = Range(Cells(Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3), MaxCol).Address).Value
BackupOne2 = Range(Cells(Range(ChkAdrs).Rows.Count - MaxPos - 2 - (t - 3), MaxCol - 1).Address).Value
Range(ChngAdrs2).Value = BackupOne2
Range(ChngAdrs1).Value = BackupOne1
End If
End If
Nxtt:
Next t


ErrHndlr:
Range("M5") = "Max Price"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub

End Sub

How to pull data from 4 different groups while staying under a set value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to pull data from 4 different groups while staying under a set value

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Re: Looking for help on a formula that I'm not sure is possible.

Thread Information

Users Browsing this Thread

Similar Threads

how to hide formula in formula box, view lookup result in formula box?

Bookmarks

Bookmarks

Posting Permissions