Concatenate VBA solution

%3CLINGO-SUB%20id%3D%22lingo-sub-1665174%22%20slang%3D%22en-US%22%3EConcatenate%20VBA%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665174%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22bbTable%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDigit%201%3C%2FTD%3E%3CTD%3EDigit%202%3C%2FTD%3E%3CTD%3EDigit%203%3C%2FTD%3E%3CTD%3EDigit%204%3C%2FTD%3E%3CTD%3EDigit%205%3C%2FTD%3E%3CTD%3EDigit%206%3C%2FTD%3E%3CTD%3EDigit%207%3C%2FTD%3E%3CTD%3EDigit%208%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E2%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E2%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E2%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E2%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E3%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E3%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E3%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E3%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E4%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E4%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E4%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E4%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E5%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E5%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E5%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E5%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E6%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E6%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E6%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E6%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E7%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E7%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E8%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E8%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E9%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E9%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E10%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E11%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E12%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E1%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%3CDIV%3E13%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CDIV%3E0%3C%2FDIV%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHi%20all%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20looking%20for%20a%20Excel%20VBA%20solution%20for%20all%20possible%20outcomes%20of%20a%20concatenate%20for%20the%20above%208%20columns%20of%20data%20with%20the%20logic%20as%20follows%20Digit%201.%20Digit%202.%20Digit%203%20.%20Digit%204%20through%20to%20digit%208%20each%20digit%20should%20be%20separated%20by%20a%20.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIn%20the%20above%20data%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%201%20will%20always%20%3D%20C%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%202%20will%20always%20%3D%201%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%203%20will%20always%20%3D%201%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%204%20could%20anything%20from%201%20to%2013%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%205%20could%20could%20anything%20from%201%20to%206%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%206%20will%20always%20%3D%200%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%207%20could%20be%20anything%20from%201%20to%209%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDigit%208%20could%20be%20anything%20from%201%20to%206%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20looking%20for%20all%20potential%20concatenates%20for%20all%20potential%20combinations%20of%20digits%201%20to%208%20eg%20C.1.1.2.3.0.2.6%20or%20C.1.1.6.2.0.1.1%20or%20C.1.1.13.6.0.9.6%20etc%20etc%2C%20.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EAny%20help%20would%20be%20greatly%20appreciated%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1665174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665534%22%20slang%3D%22de-DE%22%3ESubject%3A%20Concatenate%20VBA%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665534%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793146%22%20target%3D%22_blank%22%3E%40Walesm3011%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20fast%20solution%20without%20VBA.%20Simple%20and%20effective%2C%20I%20hope%20it%20will%20help%20you.%3C%2FP%3E%3CP%3EPlease%20see%20the%20solution%20in%20the%20inserted%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20and%20Mark%20it%20as%20Best%20Answer%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665630%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenate%20VBA%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793146%22%20target%3D%22_blank%22%3E%40Walesm3011%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22.%22%2C1%2CA2%3AH2)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1665897%22%20slang%3D%22en-US%22%3ERe%3A%20Concatenate%20VBA%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1665897%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F793146%22%20target%3D%22_blank%22%3E%40Walesm3011%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EAssuming%20your%20table%20starts%20at%20cell%20A1%2C%20the%20following%20code%20will%20generate%20the%204212%20possible%20entries%20in%20column%20%22J%22.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20concatenate()%0ADim%20i%20As%20Integer%0ADim%20j%20As%20Integer%0ADim%20k%20As%20Integer%0ADim%20l%20As%20Integer%0ADim%20m%20As%20Integer%0A%0Ai%20%3D%201%0A%0AFor%20k%20%3D%202%20To%207%0AFor%20j%20%3D%202%20To%2014%0AFor%20l%20%3D%202%20To%2010%0AFor%20m%20%3D%202%20To%207%0ACells(i%2C%2010)%20%3D%20%22c.%22%20%26amp%3B%20%221.%22%20%26amp%3B%20%221.%22%20%26amp%3B%20Cells(j%2C%204).Value%20%26amp%3B%20%22.%22%20%26amp%3B%20Cells(k%2C%205).Value%20%26amp%3B%20%22.%22%20%26amp%3B%20%220.%22%20%26amp%3B%20Cells(l%2C%207).Value%20%26amp%3B%20%22.%22%20%26amp%3B%20Cells(m%2C%208).Value%0Ai%20%3D%20i%20%2B%201%0ANext%20m%0ANext%20l%0ANext%20j%0ANext%20k%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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!