Sep 14 2020 09:19 AM
Digit 1 | Digit 2 | Digit 3 | Digit 4 | Digit 5 | Digit 6 | Digit 7 | Digit 8 |
C | 1 | 1 | 1 | 1 | 0 | 1 | 1 |
C | 1 | 1 | 2 | 2 | 0 | 2 | 2 |
C | 1 | 1 | 3 | 3 | 0 | 3 | 3 |
C | 1 | 1 | 4 | 4 | 0 | 4 | 4 |
C | 1 | 1 | 5 | 5 | 0 | 5 | 5 |
C | 1 | 1 | 6 | 6 | 0 | 6 | 6 |
C | 1 | 1 | 7 | 0 | 7 | ||
C | 1 | 1 | 8 | 0 | 8 | ||
C | 1 | 1 | 9 | 0 | 9 | ||
C | 1 | 1 | 10 | 0 | |||
C | 1 | 1 | 11 | 0 | |||
C | 1 | 1 | 12 | 0 | |||
C | 1 | 1 | 13 | 0 |
Hi all,
I am looking for a Excel VBA solution for all possible outcomes of a concatenate for the above 8 columns of data with the logic as follows Digit 1. Digit 2. Digit 3 . Digit 4 through to digit 8 each digit should be separated by a .
In the above data
Digit 1 will always = C,
Digit 2 will always = 1,
Digit 3 will always = 1,
Digit 4 could anything from 1 to 13,
Digit 5 could could anything from 1 to 6,
Digit 6 will always = 0,
Digit 7 could be anything from 1 to 9,
Digit 8 could be anything from 1 to 6
I am looking for all potential concatenates for all potential combinations of digits 1 to 8 eg C.1.1.2.3.0.2.6 or C.1.1.6.2.0.1.1 or C.1.1.13.6.0.9.6 etc etc, .
Any help would be greatly appreciated
Sep 14 2020 10:47 AM
Here is a fast solution without VBA. Simple and effective, I hope it will help you.
Please see the solution in the inserted file.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote and Mark it as Best Answer this reply if it helps please, as it will be beneficial to more Community members reading here.
Sep 14 2020 11:12 AM
Sep 14 2020 12:33 PM
Hi @Walesm3011,
Assuming your table starts at cell A1, the following code will generate the 4212 possible entries in column "J".
Sub concatenate()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
i = 1
For k = 2 To 7
For j = 2 To 14
For l = 2 To 10
For m = 2 To 7
Cells(i, 10) = "c." & "1." & "1." & Cells(j, 4).Value & "." & Cells(k, 5).Value & "." & "0." & Cells(l, 7).Value & "." & Cells(m, 8).Value
i = i + 1
Next m
Next l
Next j
Next k
End Sub