Another Excel help please ?

[sgrimshaw]

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

So, could 258.58 appear twice or more?

[Alex]

Why not make a grid that calculates every possible sum combination? Although this will only find 2 number combinations.

Copy the column of numbers and transpose across columns like this:

	1	2	3
1
2
3

Then in the first cell enter a formula similar to this and drag it across and down:

=$A2+B$2

Then you just need to match the target number in the grid.

[David A]

David A said:

Does this work?

<stuff>

I'm curious would this have worked?

[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

[JontyR]

Register1 said:

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

Dropped you back an email....should be able to get the file sorted as soon as you have it over to me.

[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

[Chester draws]

Was this not asked and answered in your other thread?

Where text to columns was the answer.

http://www.excel-easy.com/examples/text-to-columns...

[JontyR]

Register1 said:

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

You have a number of different options on here.
As already mentioned you could use Text to Columns. The problem with this is if there are any spaces within the delimiter. The example you emailed me was Name, Value, Date

so if you used text to columns you would get "Name" in the first column but " Value" and " Date" in the second/third

you could use an onscreen function.
(Cell B2) =TRIM(LEFT(A2,FIND(",",A2)-1))
(Cell C2) =TRIM(MID(A2,FIND(",",A2)+1,FIND(",",A2,FIND(",",A2)+1)-FIND(",",A2)-1))
(Cell D2) =TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2,FIND(",",A2)+1)))

Assuming you are starting the data in A2 and your delimiter is a comma this would give you Name, Value and Date in the 3 columns.

Next options are you could write your own code

Public Function ReturnColumn(oCell As Range, sDelimiter As String, lColumn As Long, Optional bTrim As Boolean = True) As String
  ReturnColumn = Split(oCell.Text, sDelimiter)(lColumn - 1)
  If bTrim Then ReturnColumn = Trim(ReturnColumn)
End Function

Public Sub SplitData()
  Dim oCell As Range

  For Each oCell In Range("A2", Range("A2").End(xlDown))
    oCell.Offset(0, 1).Resize(1, UBound(Split(oCell.Text, ", ")) + 1) = Split(oCell.Text, ", ")
  Next oCell

End Sub

You don't need both...but it doesn't appear to let me split the code.

3rd Option is you could write your own function (so use the first set module)

this you could then use instead of the previous functions...so you would
(Cell B2) =ReturnColumn($A2,",",1)
(Cell C2) =ReturnColumn($A2,",",2)
(Cell D2) =ReturnColumn($A2,",",3)

4th option is you could just write a module to do them all. (so use the second module)

note that the function needs to be put in its own module and not one of sheets or the workbook. and there is no error trapping in either of the modules so if you don't get it right it will just error and stop!