How many values are used to make a certain value.

[doogmas]

Hello, I was wondering if someone could help me with this. I have attached my workbook to help you guys understand. It is just a sample though, as the file would have been too big to upload.

Basically, in columns A,B,C and D I have 4 unique values. This goes on for about 150,000 records.
I would like to know what is the minimum amount to use to make the values in row 1(column F onwards)

For example, row 7 has values 1,2,3,9.
To make 7, it would take 3 of the values (3,3,1)
To make 8, it would take 3 of the values (3,3,2)
To make 9, it would take 1 of the values (9)

I attempted a huge IF function but it didn't come together as I thought it would.
Has anyone any ideas on how to solve this? Maybe it needs VBA?

Thanks in advance.

[shg]

What's the point of doing this?

[doogmas]

I'm currently doing a project at University. Trying to find the most efficient way to add up to numbers 1-99 using just unique 4 values.
If you ask why I'm doing this, I couldn't answer. The new breed of Mathematicians; doing things that don't need doing...

[shg]

Function HowMany(ByVal iSum As Long, rInp As Range) As Long
    Dim i           As Long
    Dim j           As Long

    For i = rInp.Cells.Count To 1 Step -1
        j = iSum \ rInp(i).Value
        iSum = iSum - rInp(i).Value * j
        HowMany = HowMany + j
    Next i
End Function

      A B C D- E F G H I J K L M N O- P- Q- R- S- T- U-
  1   A B C D    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
  2   1 2 3  4   1 1 1 1 2 2 2 2 3  3  3  3  4  4  4  4
  3   1 2 3  5   1 1 1 2 1 2 2 2 3  2  3  3  3  4  3  4
  4   1 2 3  6   1 1 1 2 2 1 2 2 2  3  3  2  3  3  3  4
  5   1 2 3  7   1 1 1 2 2 2 1 2 2  2  3  3  3  2  3  3
  6   1 2 3  8   1 1 1 2 2 2 3 1 2  2  2  3  3  3  4  2
  7   1 2 3  9   1 1 1 2 2 2 3 3 1  2  2  2  3  3  3  4
  8   1 2 3 10   1 1 1 2 2 2 3 3 3  1  2  2  2  3  3  3
  9   1 2 3 11   1 1 1 2 2 2 3 3 3  4  1  2  2  2  3  3

In F2 and copy across and down,

=HowMany(F$1, $A2:$D2)

[doogmas]

Sorry I've taken a long time to reply.
I can't thank you enough! It worked a charm!

Thanks!