Another Excel help please ?
Discussion
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:
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.
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.
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.
I think if I can get this to work, it will sort out up to 75% of the data.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
So, I will try this first.
Let you all know how it runs, R1
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.
Hi Jonty,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 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 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.
Hi Jonty,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 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
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
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
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
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
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...
Where text to columns was the answer.
http://www.excel-easy.com/examples/text-to-columns...
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
You have a number of different options on here.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
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!
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff