Find and return values based off of multiple conditions

[Dipenp]

Hello,

I wondering if there are any formulas I could use to find and return info base from 2 conditions, but it would need to return multiple values.

Products and QTY is requested:

PRODUCT QTY REQ
39573004420 220
39573004420 125
40887501820 290
40887501820 345
40887501820 200
43341404020 40

Formula to return the tag numbers for the products based on QTY in the tags, closest total to the requested QTY to be returned.

e.g.
39573004420 REQ, 220 so the following tags are shown:

273129817022021000 55
273129817021992000 55
273129817021995000 55
173217303975590000 55

PRODUCT TAG QTY
39573004420 273129817022021000 55
39573004420 273129817022021000 40
39573004420 273129817021980000 52
39573004420 273129817021980000 53
39573004420 273129817022020000 30
39573004420 273129817022020000 35
39573004420 273129817021973000 50
39573004420 273129817021973000 50
39573004420 273129817021976000 52
39573004420 273129817021981000 53
39573004420 273129817021992000 55
39573004420 273129817021995000 55
39573004420 173217303975590000 40
39573004420 173217303975590000 45
39573004420 173217303975590000 55
39573004420 173217304018652000 28
40887501820 273129817021979000 50
40887501820 173217304070303000 100
40887501820 273129817021979000 50
40887501820 273129817021979000 53
40887501820 173217304070303000 105
40887501820 273129817021981000 55
40887501820 273129817022022000 48
40887501820 273129817022020000 30
40887501820 273129817022022000 47
40887501820 273129817021981000 50
40887501820 273129817021972000 50
40887501820 273129817021981000 52
40887501820 273129817021980000 35
40887501820 273129817021980000 53
40887501820 273129817021981000 52
40887501820 173217304070303000 110
40887501820 273129817021992000 55
40887501820 273129817021995000 55
40887501820 173217303975590000 45
40887501820 273129817022015000 15
40887501820 173217303975590000 50
40887501820 173217304018652000 32
40887501820 273129807166142000 16
40887501820 173217304031263000 8
43341404020 273129817021979000 52
43341404020 273129817021981000 55
43341404020 273129817021981000 50
43341404020 273129817021980000 35
43341404020 273129817021973000 55
43341404020 273129817021973000 55
43341404020 273129817022015000 16
43341404020 273129817020341000 20


Thank you

[Limor_OP]

Hi
you mean something like that?
see attached in D2 AND ACROSS:

=IFERROR(INDEX(STOCK!B$1:B$49,AGGREGATE(15,6,(ROW(STOCK!$C$2:$C$49))/((MAX(($A$2=STOCK!$A$2:$A$49)*(STOCK!$C$2:$C$49))=STOCK!$C$2:$C$49)*($A$2=STOCK!$A$2:$A$49)),ROWS(REQUEST!$F$1:F1))),"")

[Squeaky]

Hi,

Here is my solution. I use a "sorting" method to qualify the results. The solution in the F and G columns will find the tag with the closest qty to the amount placed in E1 that matches the product placed in D1.
If there are multiple tags that meet the criteria it will display multiple results. You can hide columns H through L, but if your list is longer you will need to copy the formulas in row 49 down to the bottom of your list.
I made the following assumptions: Qty output is closest to the input in E1 whether less or greater than the input in E1. Only the closest to the value in E1 is displayed. If there are multiple outputs they will all display,
otherwise there will be a single output.

Squeaky

[Jacc]

My interpretation is that OP is after this: https://en.wikipedia.org/wiki/Subset_sum_problem

The only thing I can think of is to filter out the correct items and then use Solver with binary numbers and probably Evolutionary algorithm.

[Glenn Kennedy]

Agree. I had looked at this earlier and came to the same sort of solution (ie Solver) but then noticed that the NEXT line was for the same product, so the "stock" would have to be adjusted before moving on. At that point, I gave up!!

[Dipenp]

Thank you for your help.

[Alf]

Puzzled about the formula

Please Login or Register  to view this content.

in target cell G20. Why not use:

Please Login or Register  to view this content.

instead and then add the constraint "G20>=0". This will make the problem linear and using the Simplex method Solver will find a solution after 2 - 3 second instead of 105 seconds it takes when using the default Evolutionary setting. Even adjusting the Evolutionary time setting will only reduce the solving time to 9 - 11 seconds.

Alf

[Alf]

Having set up an alternative solution to this problem:

Used autofilter in stead of assay formula and set up a macro driven linear solver model.

To run macro "GetData" a reference must be set to solver in Visual Basic. "Developers" tab -> "Visual Basic" icon -> "Tools" -> "References" and tick box "Solver".

Start by copy product number and quantity from sheet "REQUEST" and past it to cell A2 sheet "Autofilter_Solver" then run macro "GetData". If difference (cell H2) > 0
one can run macro "RerunSolver" to get an alternative solution.

Copying range A7:B7 ("Request" tab) to A2 ("Autofilter_Solver" tab) and run "GetData" will give a solution where cell H2 > 0. Running "RerunSolver" will find an alternative
solution.

Alf