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

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.

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Phil" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

The attached example is a non VBA method using the Choose function.

Think it does what you want but only 260 records

http://cjoint.com/?hxwMAn5HEJ

VBA Noob

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Phil wrote:
> 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.

A sample construct is available at:
http://savefile.com/files/4732063
Generating unique name combinations - 3 source cols.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Phil" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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.
>

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.


--
Best regards
Leo Heuser

Followup to newsgroup only please.

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

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>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

(but Excel will auto-correct this)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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
>
> 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>)
> --

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

Wow- thanks so much everyone for the multiple options.
Phil.

"Phil" <[email protected]> skrev i en meddelelse
news:[email protected]...
>
> Wow- thanks so much everyone for the multiple options.
> Phil.
>

You are welcome, Phil, and a happy project :-)

Leo Heuser

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Superb .. It works great.
> Many thanks, and for the additional options and clarifications too, Leo
> !
> Cheers
>
You're most welcome, Max :-)

Leo