Find combination

[Kendok]

Hi there
I got this data:

The client only sends me a total of 1220 in units and 6064.40 in price, so I have to find out which rows are part of those totals.
I have tried kutools with make a number, but it only lets you choose a search, and of course there are more than 16000 options to combine the products and that add up to 1220 but of course, if I were also able to extract only those that add up to 6064.4 in this price number would be reduced by far to 1 or two options at most.
Solver is also not an option because it doesn't allow me to add more than one criteria in sum.
The truth is that I don't know how I should do it anymore, my brain has boiled.
I don't know if I explained myself...

PS when I try to use kutools to find the combinations by price it goes crazy and quits so that's not an option either

Any ideas?

 

[awoohaw]

is KUTOOLS a differerent language version of a function? If so, do you know the english name of the function?
is the price of the unit 6064.40 / 1220, what row number in the image you sent fits that scenario?

Can you use the xl2bb add in so you can paste a mini workbook with examples of the scenario? See link below.

 

[Fluff]

is KUTOOLS a differerent language version of a function?

Nope, it's an add-in from ExtendOffice.

 

[Kendok]

Libro1etgfdfgdh.xlsx
	A	B	C	D	E	F	G
1	Descrip	T1	Country	Find	T2	Unity	Price
2	001		NL			12	53,04 €
3	002		NL			12	56,88 €
4	003		NL			122	506,00 €
5	004		NL			100	285,00 €
6	005		NL			90	502,38 €
7	006		NL			60	360,00 €
8	007		NL			50	54,50 €
9	008		NL			24	61,80 €
10	009		NL			30	121,05 €
11	010		NL			30	56,40 €
12	011		NL			36	69,84 €
13	012		NL			50	110,00 €
14	013		NL			40	46,00 €
15	014		NL			65	416,50 €
16	015		NL			48	408,00 €
17	016		NL			18	44,10 €
18	017		NL			40	90,80 €
19	018		NL			70	402,50 €
20	019		NL			117	412,80 €
21	020		NL			32	440,00 €
22	021		NL			100	135,00 €
23	022		NL			144	211,68 €
24	023		NL			50	63,50 €
25	024		NL			96	168,96 €
26	025		NL			42	60,48 €
27	026		NL			30	103,20 €
28	027		NL			18	72,72 €
29	028		NL			18	72,72 €
30	029		NL			12	48,48 €
31	030		NL			180	108,00 €
32	031		NL			24	50,88 €
33	032		NL			105	51,45 €
34	033		NL			96	57,60 €
35	034		NL			400	500,00 €
36	035		NL			30	73,20 €
37	036		NL			6	51,00 €
38	037		NL			6	45,00 €
39	038		NL			50	69,00 €
40	039		NL			40	78,00 €
41	040		NL			48	477,60 €
42	041		NL			54	137,70 €
43	042		NL			804	3.708,00 €
44	043		NL			72	284,40 €
45	044		NL			120	386,40 €
46	045		NL			96	360,00 €
47	046		NL			72	109,44 €
48	047		NL			18	60,12 €
49	048		NL			20	100,40 €
50	049		NL			24	44,88 €
51	050		NL			36	36,00 €
52	051		NL			72	62,64 €
53	052		NL			112	131,68 €
54	053		NL			96	249,60 €
55	054		NL			130	189,80 €
56	055		NL			30	135,00 €
57	056		NL			90	81,00 €
58	057		NL			72	178,56 €
59	058		NL			35	96,25 €
60	059		NL			35	96,25 €
Hoja1 (2)

Libro1etgfdfgdh.xlsx
	A	B	C
1	Num Inv	Unit	Total
2	6029048	930	2.385,52 €
Hoja5

So in the first mini sheet you can see a list of products with their prices and number of units.
The customer only notifies the total number of units purchased and the total price of all of them.
NOTE: If the customer has selected any product, they have purchased all available units. And the total of these units is the content in the column "price"
Thus, if the customer has selected the item id: 44, he has purchased the 120 units for a total of 386.40.
The issue is to find all those products that may have been included in the invoice, knowing that when adding both the units and the price, it must coincide with the total of the invoice.

 

[Kendok]

Sorry, this one

Libro1etgfdfgdh.xlsx
	A	B	C
1	Num Inv	Unit	Total
2	6029050	1220	6.064,40 €
Hoja5

 

[shinigamilight]

So I was working on this problem for some time, and I was able to create a combination of 6064.40 pretty easily, the problem occurs when I try to add one more condition to it then my excel crashes. I'm using a dictionary combination, I will refine the code and try to find a solution. Dictionary with a class module is what I'm thinking right now. If I get a solution I'll get back to you. Are we allowed to have duplicate values of unit or should they all be unique?

 

[Kendok]

Unique.

 

[Legacy 143009]

I think this should work for you

Sub permutation()
  Dim lRow As Integer, targetVal As Double
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  ReDim inputt(1, lRow - 1) As String
  ReDim outputt(1, (2 ^ lRow) - 2) As String

  targetVal = 6064.40
  For i = 2 To lRow
    inputt(0, i - 2) = Cells(i, 1).Value
    inputt(1, i - 2) = Cells(i, 7).Value
  Next
  For i = 0 To lRow - 1
    outputt(0, (2 ^ i) - 1) = inputt(0, i)
    outputt(1, (2 ^ i) - 1) = inputt(1, i)
    If CDbl(outputt(1, (2 ^ i) - 1)) = targetVal Then
      Cells(1, 10).Value = outputt(0, (2 ^ i) - 1)
      Exit For
    End If
    For ii = 0 To (2 ^ i) - 2
      outputt(1, (2 ^ i) + ii) = CDbl(outputt(1, ii)) + CDbl(inputt(1, i))
      outputt(0, (2 ^ i) + ii) = outputt(0, ii) & "," & inputt(0, i)
      If CDbl(outputt(1, (2 ^ i) + ii)) = targetVal Then
        temp = Split(outputt(0, (2 ^ i) + ii), ",")
        For j = 1 To UBound(temp)
          r = Application.Match(temp(j), Range("A:A"), 0)
          Cells(j, 9).Value = temp(j)
          Range("J" & j & ":O" & j).Value = Range("B" & r & ":G" & r).Value
        Next
        Exit Sub
      End If
    Next
  Next
  MsgBox "No possible solution!"
End Sub

This will check all combinations as long as you have enough time

 

[johnnyL]

I have an idea to approach this very difficult problem, but most likely I won't be able to start looking at it until tomorrow.

 

[johnnyL]

Forgot to mention:

@Kendok Can you provide a sample result that you would be looking for? You supplied some sample data, but you haven't supplied what the result would look like for the sample data provided.