O I found this Vba online and made a few minor tweaks from what I was able to decipher. Basically it takes what is in Column A and then flips around a starting value of 0 in column B to 1 in order to try and find all possible scenarios of combinations of the values in Column A. There is a sum formula in cell C9(=SUMPRODUCT(A:A*B:B)) which it references to bounce off of the requirements set when it decides to output the result of combination of values or not.
I attached the code below. It outputs the combinations which suffice the requirements of >0 and <45500, in a comma separated string in column D.
A couple of questions on tweaking this code now-
1: I want it to apply this functionality over a larger data sheet. I want to be able to make it also output the combination values of say Column T in a comma separated value as opposed to just the column A values.
2: I want to have it essentially filter before executing this based on requirements in another column/columns. For example if I have a column F that has cities, I want to make this only choose a specific value in there before executing the rest of the code. Also the same but for another column and value such as a date in say column H
Sub Check_Consolidation()
Dim i As Long, s As String
Dim j As Long, K As Long
Dim wf As WorksheetFunction
Dim answer As String
Dim Lastrow As Long
Dim sht As Worksheet
Set wf = Application.WorksheetFunction
Set sht = ThisWorkbook.Worksheets("Sheet3")
Lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
K = 1
For i = 0 To 255
s = wf.Dec2Bin(i, 8)
'insert lastrow
For j = 1 To Lastrow
Cells(j, 2).Value = Val(Mid(s, j, 1))
Next j
If Range("C1").Value > 0 And Range("C1").Value < 45500 Then
answer = ""
'insert lastrow
For j = 1 To Lastrow
If Cells(j, 2) = 1 Then answer = answer & "," & Cells(j, 1)
Next j
Cells(K, 4) = Mid(answer, 2)
K = K + 1
End If
Next i
End Sub
I attached the code below. It outputs the combinations which suffice the requirements of >0 and <45500, in a comma separated string in column D.
A couple of questions on tweaking this code now-
1: I want it to apply this functionality over a larger data sheet. I want to be able to make it also output the combination values of say Column T in a comma separated value as opposed to just the column A values.
2: I want to have it essentially filter before executing this based on requirements in another column/columns. For example if I have a column F that has cities, I want to make this only choose a specific value in there before executing the rest of the code. Also the same but for another column and value such as a date in say column H
Sub Check_Consolidation()
Dim i As Long, s As String
Dim j As Long, K As Long
Dim wf As WorksheetFunction
Dim answer As String
Dim Lastrow As Long
Dim sht As Worksheet
Set wf = Application.WorksheetFunction
Set sht = ThisWorkbook.Worksheets("Sheet3")
Lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
K = 1
For i = 0 To 255
s = wf.Dec2Bin(i, 8)
'insert lastrow
For j = 1 To Lastrow
Cells(j, 2).Value = Val(Mid(s, j, 1))
Next j
If Range("C1").Value > 0 And Range("C1").Value < 45500 Then
answer = ""
'insert lastrow
For j = 1 To Lastrow
If Cells(j, 2) = 1 Then answer = answer & "," & Cells(j, 1)
Next j
Cells(K, 4) = Mid(answer, 2)
K = K + 1
End If
Next i
End Sub
