Concatenate VBA solution

Copper Contributor
Digit 1Digit 2Digit 3Digit 4Digit 5Digit 6Digit 7Digit 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

@Walesm3011 

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.

@Walesm3011 

As variant

=TEXTJOIN(".",1,A2:H2)

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

 

Worked a treat , thanks

@Bennadeau , worked a treat.

Many Thanks

@Walesm3011 

You're welcome!