How to generate all possible combos, and combine fields...

P

Phil

We need to generate all possible name combinations using, which for now
includes 10 first names, 26 middle initials (a-z), and 10 last
names...ie 2,600 total names- with no dupes..

If we start with the 10 first names in column/field 1 and the 26
initials in field 2 and the 10 last names in field 3, is there a single
formula or series of steps to end up with the 2600 names in a single
field, with 2600 records?

A super bonus would be a space between the first name and middle
intiial, and then a period after middle initial (which, actually, I
assume we could build into/onto the 26 initial items) and then another
space and then last name...ex. "Happy B. Smart" (these are character
names for childrens project). If we set up the intiial 26 letters in
field 2 and the 10 last names in field 3 with a space prior to each of
them, will the formula simply leave the space in place- or is it more
complicated?

Thanks.
 
B

Bernard Liengme

With:
the first names in A1:A10, Initials (A thru Z) in B1:B26, last names in
C1:C10
This macro will give the 26,000 names in column E
Sub mynames()
z = 1
For j = 1 To 10
name1 = Cells(j, 1) ' the 1 indicates
column A

For k = 1 To 26
name2 = name1 & " " & Cells(k, 2) & "." ' the 2 indicates column B

For n = 1 To 10
name3 = name2 & " " & Cells(n, 3) ' the 3 indicates column
C

Cells(z, 5) = name3 '' the 5
indicates column E
z = z + 1
Next n
Next k

Next j

End Sub


best wishes
 
M

Max

Here's a formulas play which will deliver exactly what you're after

Assuming 10 First Names listed within A1:A10,
26 Middle Initials listed within B1:B26,
10 Last Names listed within C1:C10

Put in say, E1:
=OFFSET($A$1,INT((ROW(A1)-1)/26),)&"
"&OFFSET($B$1,MOD(ROW(A1)-1,26),)&"."
Copy E1 down to E260

Col E yields an intermediate result. It concats cols A and B with the
desired space in-between and with a period after col B.

Then just put in F1:
=OFFSET($E$1,INT((ROW(A1)-1)/10),)&" "&OFFSET($C$1,MOD(ROW(A1)-1,10),)
and copy F1 down to F2600

The final required results will appear within F1:F2600.

Col F concats col E* and col C in a likewise manner,
placing the desired space in-between to yield the final required
results
*containing the concats of cols A and B
 
L

Leo Heuser

Phil said:
We need to generate all possible name combinations using, which for now
includes 10 first names, 26 middle initials (a-z), and 10 last
names...ie 2,600 total names- with no dupes..

If we start with the 10 first names in column/field 1 and the 26
initials in field 2 and the 10 last names in field 3, is there a single
formula or series of steps to end up with the 2600 names in a single
field, with 2600 records?

A super bonus would be a space between the first name and middle
intiial, and then a period after middle initial (which, actually, I
assume we could build into/onto the 26 initial items) and then another
space and then last name...ex. "Happy B. Smart" (these are character
names for childrens project). If we set up the intiial 26 letters in
field 2 and the 10 last names in field 3 with a space prior to each of
them, will the formula simply leave the space in place- or is it more
complicated?

Thanks.
Click to expand...

Hi Phil

Here's a general formula for the combinations.
Enter a random number of entries in A1:A?, B1:B? and C1:C?
Enter *all* characters (including spaces and period) in the cells.

In e.g. E1 this formula on one line:

=INDEX(A:A,ROUNDUP((MOD(ROW()-1,(COUNTA(A:A)*COUNTA(B:B)*
COUNTA(C:C)))+1)/(COUNTA(B:B)*COUNTA(C:C)),0))&INDEX(B:B,
ROUNDUP((MOD(ROW()-1,(COUNTA(B:B)*COUNTA(C:C)))+1)/COUNT(C:C),0))
&INDEX(C:C,MOD(ROW()-ROW($C$1),COUNTA(C:C))+1

Copy E1 down until the sequence starts all over again.
 
M

Max

Leo Heuser wrote:
....
=INDEX(A:A,ROUNDUP((MOD(ROW()-1,(COUNTA(A:A)*COUNTA(B:B)*
COUNTA(C:C)))+1)/(COUNTA(B:B)*COUNTA(C:C)),0))&INDEX(B:B,
ROUNDUP((MOD(ROW()-1,(COUNTA(B:B)*COUNTA(C:C)))+1)/COUNT(C:C),0))
&INDEX(C:C,MOD(ROW()-ROW($C$1),COUNTA(C:C))+1
Click to expand...

That's a great generic formula, Leo !
(Nice to see you around, btw)

Perhaps there's a typo in this part of your formula as posted:
... INDEX(B:B, ....COUNT(C:C),0))

think it should read as:
... INDEX(B:B, ....COUNTA(C:C),0))

(Admit I'm trying hard to figure out how it works, though <g>)

---
 
M

Max

... there's also a missing rightmost/closing parens: ... +1)
for the INDEX(C:C, ...)

(but Excel will auto-correct this)

---
 
L

Leo Heuser

Max said:
Leo Heuser wrote:
...

That's a great generic formula, Leo !
(Nice to see you around, btw)

Perhaps there's a typo in this part of your formula as posted:
.. INDEX(B:B, ....COUNT(C:C),0))

think it should read as:
.. INDEX(B:B, ....COUNTA(C:C),0))

(Admit I'm trying hard to figure out how it works, though <g>)
--
Click to expand...

Hi Max

Glad you like the formula :)
Thanks for pointing out the COUNTA thing!
In my testbook, I had numbers in column C, so i didn't notice.
I don't understand, where the missing right parenthesis went.
It was in my original formula.

Here are the correct formulae for 3 and 4 columns
(data in columns A:C and in columns A:D) Always start in row 1:

=INDEX(A:A,ROUNDUP((MOD(ROW()-1,(COUNTA(A:A)*COUNTA(B:B)*
COUNTA(C:C)))+1)/(COUNTA(B:B)*COUNTA(C:C)),0))&
INDEX(B:B,ROUNDUP((MOD(ROW()-1,(COUNTA(B:B)*COUNTA(C:C)))+1)/COUNTA(C:C),0))&
INDEX(C:C,MOD(ROW()-ROW($H$1),COUNTA(C:C))+1)


=INDEX(A:A,ROUNDUP((MOD(ROW()-1,(COUNTA(A:A)*COUNTA(B:B)*
COUNTA(C:C)*COUNTA(D:D)))+1)/(COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D)),0))&
INDEX(B:B,ROUNDUP((MOD(ROW()-1,(COUNTA(B:B)*COUNTA(C:C)*COUNTA(D:D)))+1)/(COUNTA(C:C)*COUNTA(D:D)),0))&
INDEX(C:C,ROUNDUP((MOD(ROW()-1,(COUNTA(C:C)*COUNTA(D:D)))+1)/COUNTA(D:D),0))&
INDEX(D:D,MOD(ROW()-ROW($H$1),COUNTA(D:D))+1)


The formulae are entered in H1. The expression ROW($H$1) is
used as a counter, so if the start cell for example is K3, please
replace ROW($H$1) by ROW($K$3)

In my original formula, I had used ROW($C$1). Actually it
should have been ROW($E$1), but as long as the row number
is the row number of the first cell, its OK.

To make the formulae simpler, one could enter in J1:J4,
=COUNTA(A:A), =COUNTA(B:B), =COUNTA(C:C) and =COUNTA(D:D)

The new formulae will then be:

in F1 and down (Data in A:B):
=INDEX(A:A,ROUNDUP((MOD(ROW()-1,PRODUCT($J$1:$J$2))+1)/$J$2,0))&
INDEX(B:B,MOD(ROW()-ROW($F$1),$J$2)+1)

In G1 and down (data in A:C):
=INDEX(A:A,ROUNDUP((MOD(ROW()-1,PRODUCT($J$1:$J$3))+1)/PRODUCT($J$2:$J$3),0))&
INDEX(B:B,ROUNDUP((MOD(ROW()-1,PRODUCT($J$2:$J$3))+1)/$J$3,0))&
INDEX(C:C,MOD(ROW()-ROW($G$1),$J$3)+1)

In H1 and down (Data in A:D):
=INDEX(A:A,ROUNDUP((MOD(ROW()-1,PRODUCT($J$1:$J$4))+1)/PRODUCT($J$2:$J$4),0))&
INDEX(B:B,ROUNDUP((MOD(ROW()-1,PRODUCT($J$2:$J$4))+1)/PRODUCT($J$3:$J$4),0))&
INDEX(C:C,ROUNDUP((MOD(ROW()-1,PRODUCT($J$3:$J$4))+1)/$J$4,0))&
INDEX(D:D,MOD(ROW()-ROW($H$1),$J$4)+1)

It's now easier to spot the pattern and expand for more columns.

Best regards
Leo
 
M

Max

Superb .. It works great.
Many thanks, and for the additional options and clarifications too, Leo
!
Cheers


---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove extra space between first and last names in merge 1
Possible to select a string in a locked cell? 1
Separate First, Last Names 2
Sometimes there a middle initial 5
How do I split Names into 2 or more fields? 1
Extracting and Separating Names in Access Query 2
Name to Initials in table 9
Separate Names 3

Top