Forum Discussion
Concatenate VBA solution
| 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
6 Replies
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 SubBennadeau , worked a treat.
Many Thanks
You're welcome!
- Worked a treat , thanks
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.
