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 (22)

### Please login or register to join the discussion.

I don't know..

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

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

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!

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.

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.

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...

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

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 ErrXITDim 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.

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

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

Can't please some people...

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

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.

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.

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.

Solver

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 :( 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.

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.

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.

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!!

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!

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.

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: