Share this content
0
22
25646

Excel - 'find me the numbers that add up to this'?

This happens to me more often than you'd think, so a shortcut would be a help!

A client (or sometimes a colleague) will present a column of numbers on Excel and note that they have claimed £x from it, but make no mention of which numbers have been used.

Case in point, a client gives me a list of 50+ items all neatly laid out.  They tell me '£2,895 of these expenses I think are private'.  Great, but which ones are they?!?  Can Excel somehow find me the cells that would add up to that total?  Or am I asking too much :)

Otherwise it is a bit of trial and error (that £3,000 expense clearly doesn't feature, but a lot of them are well under £2,895).

Replies

Please login or register to join the discussion.

avatar
13th Jun 2012 16:10

I don't know..

... but it would be a very handy function as I'm currently facing the same problem!

Thanks (0)

Have you tried...

... the "ask the client" feature?

Thanks (1)
avatar
By ACDWebb
13th Jun 2012 16:19

IIRC there is a way

using Goal Seek and some odd {Array} formulae but I never managed to get it to work in real life.

If I can find the link later I shall come back & post it

Thanks (0)
avatar
13th Jun 2012 16:22

Ask the client

@George - that's my next step!! If it was someone in the office I'd let them re-work it but as it means going back to the client it might save time!

Thanks (0)
13th Jun 2012 16:29

If your clients are anything like ours

They won't remember what numbers they used... :)

Besides, sometimes it isn't possible to go back to the client (old file or such, I'm doing work later on historic files).

Thanks (0)
avatar
13th Jun 2012 16:31

Well ...

I asked a friend of mine once if it was possible to write a programme that did just that!!

He came up with one which did the job for me (and I still have years later) but the list of numbers I had were a lot less than 50+.

The problem is the number of permutations. To give an idea, I just put in a list of 20 numbers and set a target total and found that there were over a million combinations the programme has to check and on my computer this takes a few minutes. To have a list of 50+ items could take a very long time.

Thanks (0)
avatar
13th Jun 2012 16:51

Yes ....solver

Try SOLVER - it's an Excel add-in.  I haven't used it for a couple of years now but used to when trying to fathom out a bank deposit that was made up of a variety of possible receipts which hadn't been listed properly.  The number of permutations means it can take a very long time but it's worth a go. 

 

Thanks (0)
avatar
13th Jun 2012 17:16

And next month...

Seriously, why are you doing their job for them? Smile sweetly, hand it back and say 'please can you mark the ones you think are private'.

It's the only way they'll learn...

Thanks (1)
13th Jun 2012 19:31

VBA

I used this a few years ago, and it worked well (I didn't write the code; I found it somewhere)

1) Open Excel, and open a new shettand open VBA by pressing Alt-11

2) Double click on "ThisWorkbook"Copy and paste the following code into the white space on the right

Quote:

 

Option Explicit

Function RealEqual(A, B, Optional Epsilon As Double = 0.00000001)
RealEqual = Abs(A - B) <= Epsilon
End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
If CurrRslt = "" Then ExtendRslt = NewVal _
Else ExtendRslt = CurrRslt & Separator & NewVal
End Function
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
ByVal HaveRandomNegatives As Boolean, _
ByVal CurrIdx As Integer, _
ByVal CurrTotal, ByVal Epsilon As Double, _
ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
Dim I As Integer
For I = CurrIdx To UBound(InArr)
If RealEqual(CurrTotal + InArr(I), TargetVal, Epsilon) Then
Rslt(UBound(Rslt)) = (CurrTotal + InArr(I)) _
& Separator & Format(Now(), "hh:mm:ss") _
& Separator & ExtendRslt(CurrRslt, I, Separator)
If MaxSoln = 0 Then
If UBound(Rslt) Mod 100 = 0 Then Debug.Print "Rslt(" & UBound(Rslt) & ")=" & Rslt(UBound(Rslt))
Else
If UBound(Rslt) >= MaxSoln Then Exit Sub
End If
ReDim Preserve Rslt(UBound(Rslt) + 1)
ElseIf IIf(HaveRandomNegatives, False, CurrTotal + InArr(I) > TargetVal + Epsilon) Then
ElseIf CurrIdx < UBound(InArr) Then
recursiveMatch MaxSoln, TargetVal, InArr(), HaveRandomNegatives, _
I + 1, _
CurrTotal + InArr(I), Epsilon, Rslt(), _
ExtendRslt(CurrRslt, I, Separator), _
Separator
If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
Else
'we've run out of possible elements and we _
still don't have a match
End If
Next I
End Sub
Function ArrLen(Arr()) As Integer
On Error Resume Next
ArrLen = UBound(Arr) - LBound(Arr) + 1
End Function
Function checkRandomNegatives(Arr) As Boolean
Dim I As Long
I = LBound(Arr)
Do While Arr(I) < 0 And I < UBound(Arr): I = I + 1: Loop
If I = UBound(Arr) Then Exit Function
Do While Arr(I) >= 0 And I < UBound(Arr): I = I + 1: Loop
checkRandomNegatives = Arr(I) < 0
End Function
Sub startSearch()
'The selection should be a single contiguous range in a single column. _
The first cell indicates the number of solutions wanted. Specify zero for all. _
The 2nd cell is the target value. _
The rest of the cells are the values available for matching. _
The output is in the column adjacent to the one containing the input data.

If Not TypeOf Selection Is Range Then GoTo ErrXIT
If Selection.Areas.Count > 1 Or Selection.Columns.Count > 1 Then GoTo ErrXIT
If Selection.Rows.Count < 3 Then GoTo ErrXIT

Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer, _
HaveRandomNegatives As Boolean
StartTime = Now()
MaxSoln = Selection.Cells(1).Value
TargetVal = Selection.Cells(2).Value
InArr = Application.WorksheetFunction.Transpose( _
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
HaveRandomNegatives = checkRandomNegatives(InArr)
If Not HaveRandomNegatives Then
ElseIf MsgBox("At least 1 negative number is present between positive numbers" _
& vbNewLine _
& "It may take a lot longer to search for matches." & vbNewLine _
& "OK to continue else Cancel", vbOKCancel) = vbCancel Then
Exit Sub
End If
ReDim Rslt(0)
recursiveMatch MaxSoln, TargetVal, InArr, HaveRandomNegatives, _
LBound(InArr), 0, 0.00000001, _
Rslt, "", ", "
Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
ReDim Preserve Rslt(UBound(Rslt) + 1)
Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
Application.WorksheetFunction.Transpose(Rslt)
Exit Sub
ErrXIT:
MsgBox "Please select cells in a single column before using this macro" & vbNewLine _
& "The selection should be a single contiguous range in a single column." & vbNewLine _
& "The first cell indicates the number of solutions wanted. Specify zero for all." & vbNewLine _
& "The 2nd cell is the target value." & vbNewLine _
& "The rest of the cells are the values available for matching." & vbNewLine _
& "The output is in the column adjacent to the one containing the input data."
End Sub
 

3) Close the VBA Editor

4) In cell A1 type 0

5) In cell A2 type the target number, in your case 2895

6) In cells A3, down to A52 type the individual receipt numbers

7) Highlight A1 down to A52, press Alt-F8, then click on "Run"

8) You should have a list of combinations, in the format "2895, 19:13:38, 2,3,14,26" where 2895 is your target number, 19:13:38 is the time, and 2,3,14,26 means that the combination if the 2nd, 3rd, 14th, and 26th number add up to 2895.

 

Thanks (1)
avatar
10th Jul 2015 11:36

This helped a lot
Hi,

The VBA code works nicely and does almost exactly what I want it to do. I'm using this for an intercompany reconcilliation purpose. When I use it on a series of numbers like this:

100
-100
50
-50
666
444
100
-100
-600
600
100

it gives me these results

0, 10:25:49, 1, 2
0, 10:25:49, 1, 3, 4, 8
0, 10:25:49, 1, 8
0, 10:25:49, 2, 3, 4, 7
0, 10:25:49, 2, 3, 4, 9, 10, 11
0, 10:25:49, 2, 3, 4, 11
0, 10:25:49, 2, 7
0, 10:25:49, 2, 9, 10, 11
0, 10:25:49, 2, 11
0, 10:25:49, 3, 4
0, 10:25:49, 7, 8
0, 10:25:49, 8, 9, 10, 11
0, 10:25:49, 8, 11
0, 10:25:49, 9, 10
Where I would only like it to produce one result that is telling me all the numbers numbers that add up to zero. so it should say 1, 2, 3, 4, 7, 8, 9, 10

I'm not too good with VBA but would this be possible with a slight adjustment? I would be really thankfull.

Regards, Marijn

Thanks (0)
avatar
14th Jun 2012 07:53

Neat

Would be neater still if you inserted some code that limited the amount of CPU resources devoted to the task, so that you could get on and do something else while it is running in the background.

With kind regards

Clint Westwood

Thanks (0)
avatar
15th Jun 2012 15:36

Can't please some people...

nogammonsinanundoubledgame wrote:

Would be neater still if you inserted some code that limited the amount of CPU resources devoted to the task, so that you could get on and do something else while it is running in the background.

With kind regards

Clint Westwood

 

 

Thanks (0)
avatar
15th Jun 2012 15:40

semi-automatic method...

I add 2 columns - B and C - to the right of the column in question - column A. Column B will have a 1 or a blank in and column C is column A multiplied by column B.  The bottom of Column C is the total of items you've put a 1 alongside and which you're trying to get to match your original amount.  By entering and/or deleting 1's alongside particular expense items you can quickly try all sorts of combinations.  It's not automated but it does work quite well.

Thanks (0)
avatar
15th Jun 2012 16:30

Add another

Column to the excel spreedsheet and ask the client to write which is private and which is business.

If you was really clever you could add a dropdown box so they could choose on each expense line what is private or business.

Thanks (0)
avatar
By jndavs
15th Jun 2012 18:02

Solver
I use solver to do this type of thing.
Automating AdShawBPR's idea:

You will need to install the solver addin if you do not already have it.

As an example, say your list of numbers were in A1:A50
Fill B1:B50 with zeros
in C1 type the formula =SUMPRODUCT(A1:A50,B1:B50)

Run solver (found on the 'Data tab' or 'tools menu' normally)

In the resulting dialogue box type:
Objective (Target cell): C1
Value of:2895 (in your example)
By changing values of B1:B50
Constraint: B1:B50 is binary

Click solve.

Thanks (3)
avatar
By ACDWebb
20th Jun 2012 16:31

Solver

jndavs wrote:
I use solver to do this type of thing. Automating AdShawBPR's idea: You will need to install the solver addin if you do not already have it. As an example, say your list of numbers were in A1:A50 Fill B1:B50 with zeros in C1 type the formula =SUMPRODUCT(A1:A50,B1:B50) Run solver (found on the 'Data tab' or 'tools menu' normally) In the resulting dialogue box type: Objective (Target cell): C1 Value of:2895 (in your example) By changing values of B1:B50 Constraint: B1:B50 is binary Click solve.
This is what I was trying to recall, though I have to say whenevr I have tried it, it did not work at all - I am sure that was user error though, just could never work out what :(
Thanks (0)
avatar
By jndavs
21st Jun 2012 11:50

Solver
In my experience, the most common problem is that you have to use the exact ranges.
If you only had say 15 numbers then you would need to use A1:A15 and B1:B15 in the example above.

As far as I'm aware, the solver can only handle upto a maximium of 200 numbers.

Thanks (0)
avatar
16th Jun 2012 10:14

FIO

There is an iPhone app called Figure It Out which does exactly this, although it's quite time-consuming for large quantities of numbers, as they all have to be typed in.

Thanks (0)
avatar
28th Jun 2012 10:58

Solver

For those unable to find Solver in 2003 and earlier you will have to go to Tools/Add_Ins and select Solver Add_In then click OK.

For other versions 

In Excel 2007:

Click the Office button.At the bottom of the drop-down list, click the Excel Options button.In the left pane, select Add-Ins.From the Manage: drop-down list, select Excel Add-Ins. 

In Excel 2010:

Select the File tab.In the left pane, select Options.In the left pane, select Add-Ins.From the Manage: drop-down list, select Excel Add-Ins.Click the Go... button.

For some reason it is not part of the standard installation - there are many other useful tools on that Add-In list too!! 

Thanks (0)
avatar
28th Jun 2012 11:02

Forgive an 'outsider' ...

... but isn't the advice to ask the client to do it by far the best answer?  The rest of the discussion seems to centre on "trying to do it just because it's an intriguing challenge!"

If they refuse to help you help them, do you really want them as a client?

A personal story:- At the end of my first month working in Saudi Arabia I was presented with my excruciatingly detailed mobile phone bill - entirely in Arabic, which I don't speak - and told I had a couple of hours to identify the non-business calls and total the phone charges I owed to the company.  My employers certainly weren't going to do it for me!

Thanks (0)
avatar
By Thorold
01st Jul 2012 16:43

Numbers problem.

Your client has wasted his time and yours giving you an unanalysed list of expenses. He should provide you with a schedule or schedules of the different types of expenses he is claimimg including the private expenses, and bring them forward onto a summary spreadsheet with the totals of each type of expense. The different schedules should be linked to the summary spreadsheet so that when you look at the formula in the cells of the summary sheet you can see which expenses sheet they came from. This will provide you with an automatic audit trail and will be of use to you in the future or to an external auditor.

Thanks (0)
avatar
07th Jan 2013 10:33

Excel add -in may help

You can use the SOLVER add-in however I think it only gives you the first combination of numbers that sum to your target value.

50+ transactions may be a problem because there is 2 ^ (50) combinations of numbers that need to be summed and checked against your total.

There is a shareware add-in SumMatch for Excel that may help:

http://www.SumMatch.com

 

 

 

 

 

Thanks (0)