Combinations in a string of data
Is there a function / formula I can use to determine how many combinations a
string of data can return the same value? For example: 10 15 20 5 There are 2 ways the above data can return the value 25. Many thanks |
Combinations in a string of data
Requires a macro. How big is your table?
"Eán" wrote: Is there a function / formula I can use to determine how many combinations a string of data can return the same value? For example: 10 15 20 5 There are 2 ways the above data can return the value 25. Many thanks |
Combinations in a string of data
50 lines
"Joel" wrote: Requires a macro. How big is your table? "Eán" wrote: Is there a function / formula I can use to determine how many combinations a string of data can return the same value? For example: 10 15 20 5 There are 2 ways the above data can return the value 25. Many thanks |
Combinations in a string of data
I had a similar macro that I needed to modify. I don't know how to input the
input numbers or how you want the results. Run this code and I will modify as required Public InStrings Public combo Public RowCount Public ComboLen Public NumbersMatch Public Checktotal Sub combinations() Checktotal = 25 NumbersMatch = 0 InStrings = Array(10, 15, 20, 25) Length = UBound(InStrings) + 1 Level = 1 RowCount = 1 For ComboLen = 1 To Length ReDim combo(ComboLen) Position = 0 Call recursive(Level, Position) Next ComboLen End Sub Sub recursive(ByVal Level As Integer, ByVal Position As Integer) Length = UBound(InStrings) + 1 For i = Position To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '124 '125 '234 '235 '245 '345 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = ComboLen Then For j = 0 To (ComboLen - 1) If j = 0 Then ComboString = InStrings(combo(j)) Mytotal = Val(InStrings(combo(j))) Else ComboString = ComboString & "," & InStrings(combo(j)) Mytotal = Mytotal + Val(InStrings(combo(j))) End If Next j Sheets("Sheet1").Range("A" & RowCount) = ComboString Sheets("Sheet1").Range("B" & RowCount) = Mytotal If Checktotal = Mytotal Then NumbersMatch = NumbersMatch + 1 Sheets("Sheet1").Range("C" & RowCount) = NumbersMatch End If RowCount = RowCount + 1 Else Call recursive(Level + 1, i) End If End If Next i End Sub "Eán" wrote: 50 lines "Joel" wrote: Requires a macro. How big is your table? "Eán" wrote: Is there a function / formula I can use to determine how many combinations a string of data can return the same value? For example: 10 15 20 5 There are 2 ways the above data can return the value 25. Many thanks |
Combinations in a string of data
5,10,15,20....
Are you numbers in increments of 5? 5,10,15,20,25...250 Dana DeLouis Eán wrote: 50 lines "Joel" wrote: Requires a macro. How big is your table? "Eán" wrote: Is there a function / formula I can use to determine how many combinations a string of data can return the same value? For example: 10 15 20 5 There are 2 ways the above data can return the value 25. Many thanks |
All times are GMT +1. The time now is 08:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com