Another Excel help please ?

[Register1]

I have for example column A which has the following values ( I only show 7 as an example, but there are thousands)

123.36
135.22
144.58
258.25
168.47
222.65
874.45


My question is,

How do I discover which 2 or 3 numbers add up to another number in another column, in another spread sheet, ie 258.58 some where in another spreadsheet.


As an example, here I show that the first and second number add up to 258.58

R1

[Register1]

Just values,

Thanks for any help you may have to offer.

[Register1]

mike9009 said:

Are the two/ three numbers being added together always next to each other in column A or could it be the first, third and eight number in column A?

How big is the spreadsheet you are trying to look up the number in? (ie how many numbers is it searching in?)

It does not sound that easy with simple Excel formulae but I would maybe use a VB script.


Mike

Hi Mike,

It could it be the first, third and eight number in column A, or the 101st, 201st, and 1301st
I think about 1350 lines in total.

Great if you could explain vb script, I am not really knowledgeable with excel

r1

[Register1]

paul789 said:

good luck. Sounds like you're having to "un-clusterfk" something!

EXACTLY

Outgoing girl really screwed the spread sheet up, now my supervisor asked me to "fix" it.

R1

[Register1]

utgjon said:

Full-time excel developer here...

What you're looking to do is an almost impossible task (I've tried in the past).

Establish the following:
Is there a set number of items that make up a whole?
No, it will be for example 121.21

i.e. will it always be 3 numbers?
No, 60% will be 2 numbers, 30% will be 3 numbers, and 10% will be 4 numbers.

Are the numbers NOT repeatable (can they appear twice in the same combination)?
Some target numbers may appear twice, but very very few.

Are the numbers exclusive to ONE combination?
Yes.

Are there NOT duplicate values within the dataset that make up the combination?
No


If you've answered yes to the above, you might stand a chance.

Complete the following:
Work out the total number of combinations/permutations that are possible with the dataset you have?
Create the different combinations/permutations.
Match them up.

Tried to answer your questions best I could.

R1

[Register1]

Fast and Spurious said:

Problem is with thousands of numbers that there will be multiple occurrences of 2 or 3 numbers adding up to the same figure.

Yes, this might happen


Does the total of the first set of numbers match the second? I.e is one a copy of the other albeit in summary?

Yes.


Thanks

R1

[Register1]

sgrimshaw said:

Does the second set of numbers, ie where your example of 258.58 resides, have duplicates?

So, could 258.58 appear twice or more?

No, the target number will only appear once.

Thanks,

[Register1]

JontyR said:

So I set up in Column A a list of random numbers using the formula =RandBetween(1,9999)/100
I then selected the range (in my case 585 rows) and then copied and pastevalues (otherwise the values will constantly update)
I then randomly selected 2 numbers and added them together for 5 rows in column I

Using the code below this works out all of the combinations and puts them in column J next to the sum....In each case I found the selected sum and other permutations.

Option Explicit

Public Sub FindSums()
  Dim vSums As Variant, vOptions As Variant
  Dim lSum As Long, lStart As Long, lC1 As Long
  Dim sRet As String

  vSums = Range("MySums")
  vOptions = Range("A1", Range("A1").End(xlDown))

  For lSum = LBound(vSums, 1) To UBound(vSums, 1)
    sRet = ""
    For lStart = LBound(vOptions, 1) To UBound(vOptions, 1)
      For lC1 = lStart + 1 To UBound(vOptions, 1)
        If Cells(lStart, 1) + Cells(lC1, 1) = vSums(lSum, 1) Then sRet = sRet & "|" & "A" & lStart & " + A" & lC1
      Next lC1
    Next lStart

    Cells(lSum, 10) = Mid(sRet, 2)

  Next lSum

End Sub

I think if I can get this to work, it will sort out up to 75% of the data.

So, I will try this first.

Let you all know how it runs, R1

[Register1]

JontyR said:

So I set up in Column A a list of random numbers using the formula =RandBetween(1,9999)/100
I then selected the range (in my case 585 rows) and then copied and pastevalues (otherwise the values will constantly update)
I then randomly selected 2 numbers and added them together for 5 rows in column I

Using the code below this works out all of the combinations and puts them in column J next to the sum....In each case I found the selected sum and other permutations.

Option Explicit

Public Sub FindSums()
  Dim vSums As Variant, vOptions As Variant
  Dim lSum As Long, lStart As Long, lC1 As Long
  Dim sRet As String

  vSums = Range("MySums")
  vOptions = Range("A1", Range("A1").End(xlDown))

  For lSum = LBound(vSums, 1) To UBound(vSums, 1)
    sRet = ""
    For lStart = LBound(vOptions, 1) To UBound(vOptions, 1)
      For lC1 = lStart + 1 To UBound(vOptions, 1)
        If Cells(lStart, 1) + Cells(lC1, 1) = vSums(lSum, 1) Then sRet = sRet & "|" & "A" & lStart & " + A" & lC1
      Next lC1
    Next lStart

    Cells(lSum, 10) = Mid(sRet, 2)

  Next lSum

End Sub

Hi Jonty,

I did a test of 585 rows.
Random numbers in column A
Then I copied, and paste special these into column B
Then I deleted row A, so the pasted number that were in row B, are now in row A

Then to keep it simple
I added the number in row one, plus the number in row two, and put that value into column I row 1

I added the number in row three, plus the number in row four, and put that value into column I row 2

I added the number in row five, plus the number in row six, and put that value into column I row 3

I added the number in row seven, plus the number in row eight, and put that value into column I row 4

I added the number in row nine, plus the number in row ten, and put that value into column I row 5

I opened VB with Alt f11, and pasted the whole gubbins into the editor.
Then saved it as a Macro enabled work book.
Then I shut excel.
Then opened my workbook again.
Pressed Alt f8, and I see the macro "find sums"
Pressed the "run"

Then it says, Run Time Error
Method Range of object global failed.

When I press debug, it highlights vSums = Range("MySums")

Stuck,

I sent a pm.

R1

[Register1]

Hi Jonty

Sent you a made up sample

r1

[Register1]

Thanks Month,
Seems to work just fine.
It finds the two number combinations, and the three number combinations.

Could you pleaseedit the macro, reverting back to just finding pairs of numbers?
I feel that finding trios of numbers, grinds it to a halt, when I feed it more than 100 rows.

Thanks,

R1

[Register1]

Hi Jonty,

Looks like I got the first line of my post above all wrong.

Your program worked great,
Sorted out all the "two" value numbers equalling the target, then sorted out the "trio" of numbers

Thanks.


How I ended up calling you "month" I will never know.
Blame it on working 12 hour night shifts.

Would you like another smaller challenge?

From spreadsheet First text value date

John 245.21 02/05/2017
Mary 117.33 03/05/2017
Stuart 21.65 04.05.2017
Simon 54.21 05.05.17

Column A has three terms in it..
Name, then a value, then a date

Is there a way to make each of these three terms move into, or copy into columns B, C, D.

ie, Names into column B
Value into column C
Date into column D

Register 1

Edited by Register1 on Wednesday 3rd May 08:11