Formula to lookup value based on criteria (1 Viewer)

[Andy Teal]

Hello,

I have been on this my whole day. Still could not figure out a way.

I need to lookup some values from a list for instance: A2:A10 based on the value of B2. The value of B2 must be the sum total of the filtered values. In the below example, I need to filter out the data whose sum total will be 30. Eventually, the output should be 10, 15 and 5. Is this possible using Excel formula? If not, can you please share a VBA code to do this?

Thank you in advance!

 

[Ranman256]

you can put a ComboBox so when user picks the item , it assigns B2, then filters the main data

Sub Filter1()
Dim rng As Range
Dim vMyFlt

vMyFlt = Range("B2").Value

' Selection.AutoFilter    'turn off existing filter

Sheets("MainData").Select
Range("A1").Select
Set rng = Sheets("MainData").UsedRange
   
    Selection.AutoFilter

      'here it filters on column 7 w value in B2   
    rng.AutoFilter Field:=7, Criteria1:=vMyFlt
End Sub

 

[Andy Teal]

you can put a ComboBox so when user picks the item , it assigns B2, then filters the main data

Sub Filter1()
Dim rng As Range
Dim vMyFlt

vMyFlt = Range("B2").Value

' Selection.AutoFilter    'turn off existing filter

Sheets("MainData").Select
Range("A1").Select
Set rng = Sheets("MainData").UsedRange
  
    Selection.AutoFilter

      'here it filters on column 7 w value in B2  
    rng.AutoFilter Field:=7, Criteria1:=vMyFlt
End Sub

It is not working as expected. It could not figure out what to filter that sums up the criteria. Do I need to modify the code?

 

[The_Doc_Man]

Note that the combination of 10, 12, and 8 would also meet your criteria.

You are doing a problem in combination theory (combinatorials) and that is frequently quite tough. Particularly when the solution is not unique.

You need to consider more thoroughly what you are looking for and specify that very carefully.

 

[Andy Teal]

Note that the combination of 10, 12, and 8 would also meet your criteria.

You are doing a problem in combination theory (combinatorials) and that is frequently quite tough. Particularly when the solution is not unique.

You need to consider more thoroughly what you are looking for and specify that very carefully.

I agree and apologize for the misunderstanding. The given data is supposed to be an example. My main data, however, meets the criteria once. Is it possible to find out the data in A1:A10 that sums up the criteria in C3 with Excel functions or formulas?

 

[Gasman]

I am not knocking the experts here, but when I need anything Access I come here. If I need anything Excel, my first point of call is here.

If you do post there, do mention you have crossposted here as they take that seriously over there.
Also if you get a solution over there, please mark this thread as solved.

 

[The_Doc_Man]

I agree and apologize for the misunderstanding. The given data is supposed to be an example. My main data, however, meets the criteria once. Is it possible to find out the data in A1:A10 that sums up the criteria in C3 with Excel functions or formulas?

There are no direct functions to do this. You COULD create code in the form of VBA functions that would attempt to find your desired resulting set of values to reach your desired total. If there is any ambiguity, though, you would have to consider whether you even wanted to know if there are multiple solutions.

But there is ANOTHER issue here... reporting. I said you could build a VBA function to do this - but you would have multiple values to return and a true function, to be syntactically correct, only returns a single value. You potentially have three items to be returned, so this would have to be a SUB of some type with ByRef argument abilities OR the ability to directly alter the appearance of the cells to show what you found.

Can you do this? I have no doubt that it CAN be done and I have no doubt that you CAN do this. But it might get a bit complex. Factors to ask yourself before you attack this:

1. Will your criteria be met if it takes two or four values to total up to your criterion - but not three?

2. What do you want done if no three-digit combination (of any length) totals up to your criterion?

3. What do you want done if more than one combination can total up to match your criterion?

4. What method of specifying the list's range and the cell for the criterion would be used?

You will need to answer each question before you start.

 

[arnelgp]

I have been on this my whole day. Still could not figure out a way.

it's a big code you need there, you need to combination (non-repeating) 2 to 6 numbers and check each combination if the total is 30.

 

[Andy Teal]

it's a big code you need there, you need to combination (non-repeating) 2 to 6 numbers and check each combination if the total is 30.

Yes, we might need to check every possibility. But will it take too long to run the code?

And, thanks everyone for your helpful suggestions.