Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating "factorial" result from three lists...
We want to create fictional character names for a children's project,
and have selected 30 first names, 30 last names, and want to include 26 (a-z) middle initials. Therefore there are 30x30x26 = 23,400 possible unique names in the format of "First MI Last" such as "James B. Happy" that we wish to create and print a few hundred at a time onto paper dolls. (We have 30 other first names for females, and can just do the required operation twice, so please ignore that complicating factor) Question is how can we take these three fields of 26, 30 and 30 records each, in either Excel 2000 or Access 2000 and end up with the 23400 names in a single field or comma delimited form. Our futile attempt yesterday had us researching combing "strings" and "concatenation" and "factorial functions"- and we couldnt figure it out. Do we start with one table with three fields, or three tables with one field each. Where do we input the required function? Thanks. |
#2
|
|||
|
|||
This has been answered many times so:
Use Goggle's Newsgroup search; In advanced specify *excel* (with the *s) as the newsgroup and combinations as the subject. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Phil" wrote in message oups.com... We want to create fictional character names for a children's project, and have selected 30 first names, 30 last names, and want to include 26 (a-z) middle initials. Therefore there are 30x30x26 = 23,400 possible unique names in the format of "First MI Last" such as "James B. Happy" that we wish to create and print a few hundred at a time onto paper dolls. (We have 30 other first names for females, and can just do the required operation twice, so please ignore that complicating factor) Question is how can we take these three fields of 26, 30 and 30 records each, in either Excel 2000 or Access 2000 and end up with the 23400 names in a single field or comma delimited form. Our futile attempt yesterday had us researching combing "strings" and "concatenation" and "factorial functions"- and we couldnt figure it out. Do we start with one table with three fields, or three tables with one field each. Where do we input the required function? Thanks. |
#3
|
|||
|
|||
Phil,
With only three sets of date, it is pretty simple. Fill in your names in columns B, C and D starting in row one. Run the following code... '--------------------------------------------------- Sub CreateNewNames() 'Creates a single list that combines the values in three separate lists. 'Jim Cone - San Francisco, USA - Feb 25, 2005 Dim rngFirst As Excel.Range Dim rngSecond As Excel.Range Dim rngThird As Excel.Range Dim rngF As Excel.Range Dim rngS As Excel.Range Dim rngT As Excel.Range Dim lngRow As Long Const strSpace As String = " " lngRow = 1 Set rngFirst = Range("B1:B30") Set rngSecond = Range("C1:C26") Set rngThird = Range("D1:D30") Application.ScreenUpdating = False For Each rngF In rngFirst For Each rngS In rngSecond For Each rngT In rngThird Cells(lngRow, 6).Value = rngF.Value & strSpace & rngS.Value & _ strSpace & rngT.Value lngRow = lngRow + 1 Next 'rngT Next 'rngS Next 'rngF Application.ScreenUpdating = True Set rngFirst = Nothing Set rngSecond = Nothing Set rngThird = Nothing End Sub '---------------------------------- Regards, Jim Cone San Francisco, USA "Phil" wrote in message oups.com... We want to create fictional character names for a children's project, and have selected 30 first names, 30 last names, and want to include 26 (a-z) middle initials. Therefore there are 30x30x26 = 23,400 possible unique names in the format of "First MI Last" such as "James B. Happy" that we wish to create and print a few hundred at a time onto paper dolls. (We have 30 other first names for females, and can just do the required operation twice, so please ignore that complicating factor) Question is how can we take these three fields of 26, 30 and 30 records each, in either Excel 2000 or Access 2000 and end up with the 23400 names in a single field or comma delimited form. Our futile attempt yesterday had us researching combing "strings" and "concatenation" and "factorial functions"- and we couldnt figure it out. Do we start with one table with three fields, or three tables with one field each. Where do we input the required function? Thanks. |
#4
|
|||
|
|||
Thanks. Jim. Bernard, sometimes coming up with the search term is the hard part..."combinations" does come up with useful results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Amount or Numbers in Words | New Users to Excel | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Creating Combinations from Two Lists | Excel Discussion (Misc queries) | |||
Spellnumber | Excel Worksheet Functions |