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

[Nigel_O]

Does the other column in the other spreadsheet contain formulae, or just values?

[Register1]

Just values,

Thanks for any help you may have to offer.

[mike9009]

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

[flight147z]

I've used "solver" in the past for this

http://www.k2e.com/tech-update/tips/147-using-exce...

[paul789]

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

[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

[mike9009]

Register1 said:

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

VB (visual basic) script is not easy to explain on a forum and not a five minute thing you can learn. But it sounds like an intriguing mathematical problem......

If no one can offer a simpler solution and you are able to share the spreadsheet (ie. no data protection issues) then I could give it a whirl one evening (not promising anything!)...... I would need to ask a few more questions to confirm the script would work prior to attempting it too.

(sad as it sounds, I quite regularly write scripts for work colleagues at home because I enjoy it )

PM me if you want, as to do this manually sounds like an impossible task......


Mike

[JontyR]

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

[Chester draws]

Guessing it's too late for Ctrl+Z...

Or on a more serious note, any backups from before the muckup?

Edited by Chester draws on Thursday 27th April 13:03

[Fast and Spurious]

Problem is with thousands of numbers that there will be multiple occurrences of 2 or 3 numbers adding up to the same figure. Does the total of the first set of numbers match the second? I.e is one a copy of the other albeit in summary?

[mike9009]

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

Stop spoiling my fun ... I think your code is far more succinct than the garbled rubbish I would have created

[JontyR]

mike9009 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

Stop spoiling my fun ... I think your code is far more succinct than the garbled rubbish I would have created

Sorry about that....it's rare I get in on an excel one before someone (like you ) answers it first

[utgjon]

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? i.e. will it always be 3 numbers?
Are the numbers NOT repeatable (can they appear twice in the same combination)?
Are the numbers exclusive to ONE combination?
Are there NOT duplicate values within the dataset that make up the combination?


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.

[David A]

Does this work?

Put the values all in a one column in a sql table (table named allvalues and column named a)
1071 is the value your after.
Crappy SQL and quick and dirty but it works.

If you wanted to you could use a query within Excel and Excel as a source if you didn't want to use SQL or access I guess.

select *, allvalues.a+b.a+c.a , b.a+c.a , allvalues.a+c.a from allvalues
cross join allvalues as b
cross join allvalues as c
where allvalues.a+b.a+c.a = 1071
or b.a+c.a = 1071
or allvalues.a+c.a=1071
order by 1

a a a (No column name) (No column name) (No column name)
46 197 828 1071 1025 874
46 828 197 1071 1025 243
46 839 232 1117 1071 278
46 232 839 1117 1071 885
75 765 231 1071 996 306
75 839 232 1146 1071 307
75 232 839 1146 1071 914
75 231 765 1071 996 840
107 839 232 1178 1071 339
107 232 839 1178 1071 946
107 482 482 1071 964 589
194 224 653 1071 877 847
194 839 232 1265 1071 426
194 232 839 1265 1071 1033
194 653 224 1071 877 418
194 283 594 1071 877 788
194 594 283 1071 877 477
197 828 46 1071 874 243
197 839 232 1268 1071 429
197 232 839 1268 1071 1036
197 46 828 1071 874 1025
197 567 307 1071 874 504
197 307 567 1071 874 764
200 839 232 1271 1071 432
200 232 839 1271 1071 1039
200 839 232 1271 1071 432
200 232 839 1271 1071 1039
224 194 653 1071 847 877
224 839 232 1295 1071 456
224 232 839 1295 1071 1063
224 653 194 1071 847 418
228 839 232 1299 1071 460
228 232 839 1299 1071 1067
231 765 75 1071 840 306
231 75 765 1071 840 996
231 839 232 1302 1071 463
231 232 839 1302 1071 1070
232 197 839 1268 1036 1071
232 501 839 1572 1340 1071
232 765 839 1836 1604 1071
232 675 839 1746 1514 1071
232 966 839 2037 1805 1071
232 224 839 1295 1063 1071
232 593 839 1664 1432 1071
232 407 839 1478 1246 1071
232 558 839 1629 1397 1071
232 200 839 1271 1039 1071
232 194 839 1265 1033 1071
232 75 839 1146 914 1071
232 828 839 1899 1667 1071
232 200 839 1271 1039 1071
232 839 839 1910 1678 1071
232 839 232 1303 1071 464
232 485 839 1556 1324 1071
232 279 839 1350 1118 1071
232 947 839 2018 1786 1071
232 232 839 1303 1071 1071
232 455 839 1526 1294 1071
232 805 839 1876 1644 1071
232 710 839 1781 1549 1071
232 989 839 2060 1828 1071
232 653 839 1724 1492 1071
232 46 839 1117 885 1071
232 688 839 1759 1527 1071
232 832 839 1903 1671 1071
232 341 839 1412 1180 1071
232 231 839 1302 1070 1071
232 283 839 1354 1122 1071
232 107 839 1178 946 1071
232 829 839 1900 1668 1071
232 985 839 2056 1824 1071
232 482 839 1553 1321 1071
232 228 839 1299 1067 1071
232 594 839 1665 1433 1071
232 500 839 1571 1339 1071
232 442 839 1513 1281 1071
232 567 839 1638 1406 1071
232 774 839 1845 1613 1071
232 893 839 1964 1732 1071
232 722 839 1793 1561 1071
232 484 839 1555 1323 1071
232 659 839 1730 1498 1071
232 307 839 1378 1146 1071
279 839 232 1350 1071 511
279 485 307 1071 792 586
279 232 839 1350 1071 1118
279 307 485 1071 792 764
283 194 594 1071 788 877
283 839 232 1354 1071 515
283 232 839 1354 1071 1122
283 594 194 1071 788 477
307 197 567 1071 764 874
307 839 232 1378 1071 539
307 485 279 1071 764 586
307 279 485 1071 764 792
307 232 839 1378 1071 1146
307 567 197 1071 764 504
341 839 232 1412 1071 573
341 232 839 1412 1071 1180
407 839 232 1478 1071 639
407 232 839 1478 1071 1246
442 839 232 1513 1071 674
442 232 839 1513 1071 1281
455 839 232 1526 1071 687
455 232 839 1526 1071 1294
482 839 232 1553 1071 714
482 232 839 1553 1071 1321
482 107 482 1071 589 964
482 482 107 1071 589 589
484 839 232 1555 1071 716
484 232 839 1555 1071 1323
485 839 232 1556 1071 717
485 279 307 1071 586 792
485 232 839 1556 1071 1324
485 307 279 1071 586 764
500 839 232 1571 1071 732
500 232 839 1571 1071 1339
501 839 232 1572 1071 733
501 232 839 1572 1071 1340
558 839 232 1629 1071 790
558 232 839 1629 1071 1397
567 197 307 1071 504 874
567 839 232 1638 1071 799
567 232 839 1638 1071 1406
567 307 197 1071 504 764
593 839 232 1664 1071 825
593 232 839 1664 1071 1432
594 194 283 1071 477 877
594 839 232 1665 1071 826
594 232 839 1665 1071 1433
594 283 194 1071 477 788
653 224 194 1071 418 847
653 194 224 1071 418 877
653 839 232 1724 1071 885
653 232 839 1724 1071 1492
659 839 232 1730 1071 891
659 232 839 1730 1071 1498
675 839 232 1746 1071 907
675 232 839 1746 1071 1514
688 839 232 1759 1071 920
688 232 839 1759 1071 1527
710 839 232 1781 1071 942
710 232 839 1781 1071 1549
722 839 232 1793 1071 954
722 232 839 1793 1071 1561
765 75 231 1071 306 996
765 839 232 1836 1071 997
765 232 839 1836 1071 1604
765 231 75 1071 306 840
774 839 232 1845 1071 1006
774 232 839 1845 1071 1613
805 839 232 1876 1071 1037
805 232 839 1876 1071 1644
828 197 46 1071 243 874
828 839 232 1899 1071 1060
828 232 839 1899 1071 1667
828 46 197 1071 243 1025
829 839 232 1900 1071 1061
829 232 839 1900 1071 1668
832 839 232 1903 1071 1064
832 232 839 1903 1071 1671
839 197 232 1268 429 1071
839 501 232 1572 733 1071
839 765 232 1836 997 1071
839 675 232 1746 907 1071
839 966 232 2037 1198 1071
839 224 232 1295 456 1071
839 593 232 1664 825 1071
839 407 232 1478 639 1071
839 558 232 1629 790 1071
839 200 232 1271 432 1071
839 194 232 1265 426 1071
839 75 232 1146 307 1071
839 828 232 1899 1060 1071
839 200 232 1271 432 1071
839 839 232 1910 1071 1071
839 485 232 1556 717 1071
839 279 232 1350 511 1071
839 947 232 2018 1179 1071
839 232 839 1910 1071 1678
839 232 232 1303 464 1071
839 455 232 1526 687 1071
839 805 232 1876 1037 1071
839 710 232 1781 942 1071
839 989 232 2060 1221 1071
839 653 232 1724 885 1071
839 46 232 1117 278 1071
839 688 232 1759 920 1071
839 832 232 1903 1064 1071
839 341 232 1412 573 1071
839 231 232 1302 463 1071
839 283 232 1354 515 1071
839 107 232 1178 339 1071
839 829 232 1900 1061 1071
839 985 232 2056 1217 1071
839 482 232 1553 714 1071
839 228 232 1299 460 1071
839 594 232 1665 826 1071
839 500 232 1571 732 1071
839 442 232 1513 674 1071
839 567 232 1638 799 1071
839 774 232 1845 1006 1071
839 893 232 1964 1125 1071
839 722 232 1793 954 1071
839 484 232 1555 716 1071
839 659 232 1730 891 1071
839 307 232 1378 539 1071
893 839 232 1964 1071 1125
893 232 839 1964 1071 1732
947 839 232 2018 1071 1179
947 232 839 2018 1071 1786
966 839 232 2037 1071 1198
966 232 839 2037 1071 1805
985 839 232 2056 1071 1217
985 232 839 2056 1071 1824
989 839 232 2060 1071 1221
989 232 839 2060 1071 1828

[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

[JontyR]

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? i.e. will it always be 3 numbers?
Are the numbers NOT repeatable (can they appear twice in the same combination)?
Are the numbers exclusive to ONE combination?
Are there NOT duplicate values within the dataset that make up the combination?


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.

Or just use the code I wrote slightly above and it works a treat. PS....Ive been a fulltime excel developer since 1994

[utgjon]

JontyR said:

Or just use the code I wrote slightly above and it works a treat. PS....Ive been a fulltime excel developer since 1994

Except your code doesn't handle more than a combination of 2 numbers or duplicates...hence why i was asking the questions I was.