VBA first name + last name generator (all combinations)

[fomenter]

I wonder how I can build a code that generate all possible combination scenarios

Example: Taylor (First Name) Martines (Last Name)

so, i need a generator which can create all possible combination scenarios using the first and last name letters

e.g. Tmartines
Taymartines
taylormartines
tmart
marttay
mtaylor
Tines
etc..

Is it easy to do it?Help is appreciated

Thanks

 

[mrmmickle1]

Your question seems a little vague. Do you want to iterate through all possible combinations of those alphabetical characters? Can you give us the Name and all expected results for it? Also will all names have only first and last name or do some have middle initials? In short: How is your data set up?

Here is a thread I answered a few weeks ago that does something similar....maybe this will help get you started:

http://www.mrexcel.com/forum/excel-questions/939043-there-visual-basic-applications-solution.html

This may be another helpful approach to parse out possibilities.

Excel 2010
	A	B	C	D	E
1	Number	First	Last		Taylor Martines
2	1	T	M
3	2	Ta	Ma
4	3	Tay	Mar
5	4	Tayl	Mart
6	5	Taylo	Marti
7	6	Taylor	Martin
8	7		Martine
9	8		Martines
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2>=FIND(" ",$E$1,1),"",MID($E$1,1,A2))
C2	=IF(A2>LEN($E$1)-FIND(" ",$E$1,1),"",MID(MID($E$1,FIND(" ",$E$1,1)+1,LEN($E$1)-FIND(" ",$E$1,1)),1,A2))

 

[fomenter]

Your question seems a little vague. Do you want to iterate through all possible combinations of those alphabetical characters? Can you give us the Name and all expected results for it? Also will all names have only first and last name or do some have middle initials? In short: How is your data set up?

Here is a thread I answered a few weeks ago that does something similar....maybe this will help get you started:

http://www.mrexcel.com/forum/excel-questions/939043-there-visual-basic-applications-solution.html

This may be another helpful approach to parse out possibilities.

Excel 2010

	A	B	C	D	E
Number	First	Last	Taylor Martines
T	M
Ta	Ma
Tay	Mar
Tayl	Mart
Taylo	Marti
Taylor	Martin
	Martine
	Martines

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(A2>=FIND(" ",$E$1,1),"",MID($E$1,1,A2))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(A2>LEN($E$1)-FIND(" ",$E$1,1),"",MID(MID($E$1,FIND(" ",$E$1,1)+1,LEN($E$1)-FIND(" ",$E$1,1)),1,A2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the reply.

So, let's assume that I have the following names (no middle initial)
[TABLE="width: 1062"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 6"]First Named/Parsed[/TD]
[TD][/TD]
[TD="colspan: 8"]Last Name Parsed[/TD]
[/TR]
[TR]
[TD]Taylor Martines[/TD]
[TD]T[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD]L[/TD]
[TD]O[/TD]
[TD]R[/TD]
[TD][/TD]
[TD]M[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]T[/TD]
[TD]I[/TD]
[TD]N[/TD]
[TD]E[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]David Ruis[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]V[/TD]
[TD]I[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]U[/TD]
[TD]I[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sendan Trank[/TD]
[TD]S[/TD]
[TD]E[/TD]
[TD]N[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]T[/TD]
[TD]R[/TD]
[TD]A[/TD]
[TD]N[/TD]
[TD]K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ari Sap[/TD]
[TD]A[/TD]
[TD]R[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]A[/TD]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Ari Sap A R I S A P

Ex:If we take the last sample ("ARI"), we can parse, scramble the word of "ARI" in the following ways

A
R
I
AR
AI
RA
RI
IA
IR
AIR
ARI
IRA
IAR
RIA
RAI


so I need all possible combinations of the A-R-I letters, and do the same thing for last name (independent from first name). I want to do this for all the names that I have in my list.

 

[shg]

I think you have a tiger by the tail.

"Taylor" is 6 letters without repeats. There are 1,956 permutations of one to six letters.

"Martines" is 8 letters without repeats. There are 109,600 permutations of one to eight letter letters.

There are 214M ways to combine those permutations.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

First​

[/td][td]Taylor[/td][td]

1,956​

[/td][td]C1: {=SUM(PERMUT(LEN(B1), ROW(INDIRECT("1:" & LEN(B1)))))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Last​

[/td][td]Martines[/td][td]

109,600​

[/td][td]C2: {=SUM(PERMUT(LEN(B2), ROW(INDIRECT("1:" & LEN(B2)))))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td][/td][td]

214,377,600​

[/td][td]C3: =C1*C2[/td][/tr]
[/table]

When you get to Throckmorton Glindenphumtz, only God could calculate the possibilities.

 

[mrmmickle1]

Based on shg's post. It seems this is an exercise in futility. Maybe you can explain what your trying to do and someone could offer up a solution within reason.

 

[kalak]

Ex:If we take the last sample ("ARI"), we can parse, scramble the word of "ARI" in the following ways

A
R
I
AR
AI
RA
RI
IA
IR
AIR
ARI
IRA
IAR
RIA
RAI


so I need all possible combinations of the A-R-I letters, and do the same thing for last name (independent from first name). I want to do this for all the names that I have in my list.

Just for interest, with that particular example you can use something like the VBA code below.

But it's obvious that one runs out of computer capacity very quickly as the project gets larger.

Sub combstuff()

Dim c(), y(), b() As Boolean
Dim u&, i&, j&, s&, v&, x&
a = Array("A", "R", "I", "X", "Y", "Z")
u = UBound(a) + 1: s = 1

For v = 1 To u
s = s + x: x = 0
ReDim y(1 To u ^ v, 1 To v), c(1 To u ^ v, 1 To v)
For j = 1 To v
    For i = 1 To u ^ v
        y(i, j) = 1 + Int((i - 1) / (u ^ (v - j))) Mod u
    Next i
Next j

For i = 1 To u ^ v
    ReDim b(60)
    For j = 1 To v
        If Not b(y(i, j)) Then b(y(i, j)) = True Else GoTo nxti
    Next j
    x = x + 1
    For j = 1 To v
        c(x, j) = a(y(i, j) - 1)
    Next j
nxti:
Next i
Cells(s, 1).Resize(x, v).Value = c
Next v

End Sub