Is this possible - auto fill question

I'm not sure if this is possible, but I figured this is the place to find out. So here goes. The end result of my Excel sheet will be 3 columns, which I will eventually send to .csv format.

I have Main Entities (call them numbers), sub entities of the number (uppercase letters), and sub-entities of that (lowercase letters). Confused yet?

I want a prompt that asks how many main entites the user has......i.e. 3
and how many subentities....say 4
and how many sub,sub entities....2

Based on these I want a sheet that produces the following results:
1,1,1
1,1,2
1,2,1
1,2,2
1,3,1
1,3,2
1,4,1
1,4,2
2,1,1
2,1,2
2,2,1
2,2,2
2,3,1
2,3,2
2,4,1
2,4,2

So the first three columns of the sheet present every possible combination.

If this is possible, can anyone tell me how? Thanks!!!

It is possible with a macro

This macro gives the result that you said you required. I do not understand your mention of capital and lower case letters in your post as you make no mention of them in the past that deals with - want a sheet that produces the following results

Sub FillNumbers()
Dim sNum As String
Dim iMain As Integer
Dim iSub As Integer
Dim iSubSub As Integer
Dim iRow As Integer

sNum$ = InputBox("How many main entites do you have?")
If sNum = "" Then
Exit Sub
End If
iRow = 1
For iMain = 1 To Val(sNum) Step 1
For iSub = 1 To Val(sNum) Step 1
For iSubSub = 1 To Val(sNum) Step 1
Cells(iRow, "a").Value = iMain
Cells(iRow, "b").Value = iSub
Cells(iRow, "c").Value = iSubSub
iRow = iRow + 1
Next iSubSub
Next iSub
Next iMain
End Sub

Mudraker...thanks for the response. Don't worry about the letters I mentioned. I was going to explain it a different way. That works fine for the main entities, but I also need to specify the number of sub entities and sub,sub entities (you know what I mean). Then based on that....generate the three columns.
Can you help?

A person has 3 agencies structred as follows:

Agency 1
---Branch 1
---Branch 2
---Branch 3
Each branch has 3 departments.
Agency 2
---Branch 1
Each Branch has 3 departments also
Agency 3
---Branch 1
Each branch has 2 departments

I need to list every possible combination for these....Agency would be column 1, branch would be 2, and dept. would be 3. In this case it would be:
1,1,1
1,1,2
1,1,3
1,2,1
1,2,2
1,2,3
1,3,1
1,3,2
1,3,3
2,1,1
2,1,2
2,1,3
3,1,1
3,1,2

Rather than manually entering this information in I was hopingto have some form the user fills out and it generates this output.

Any ideas?

Hi

I have made a little spreadsheet which kinda does what you want.

You have to enter the Branch and Departments for each Agency. Whereas in your first post you wanted to just number of Agencies, Branches and Departments? From that it is hard to determine how many per Agency, so it has to be done this way I think.

Anyway it is attached, not very well formatted but if it does the job and you want it altering I don't mind fixing it up then.

Michael.

Thanks for the submission!!! Two questions:
1. I need the results to display the agency, branch, department info in seperate columns and I don't need the spaces between the departments or agencies.

2. Can I get the results to print on a seperate sheet because I am going to export in comma delimited form so it would be easier to just have a sheet iwth the result.

So Column A would be Agnecy number, B branch, and C department.


Thanks so much!!!

I was able to get the results to a seperate sheet and seperatwe the columns! Thanks again! I do have one more question though. After entity 9, it goes to A then B then C...so on. Is there code that would support this?

Have a look at the attached spread sheet

It uses a variation on my 1st posted code and a user form to ask for how many departments.

With the department field it lists department numbers 1 to 9 as 1 to 9 and any number greater then 9 as A, B, C etc

Again...thanks for ALL YOUR HELP!!!

The problem I see with the new code is it doesn't all the agencies to have a different amount of branches or departments. For example agency 1 with 2 branches, 2 departments, but when you add agency 2 with 3 departments it wll add a third department to agency 1.

I got your original to a point where it exports the data to a second sheet in columns A, B, C. I would like to get code that automatically converts number over nine to the alphabet (it's all three categories that go to the alphabet).

Now, I'm happy with that. It's great. Example output on my second sheet is (commas indicate a new column)
1,1,1=
1,1,2=

I put the equal sign in there so when I send it to .csv it will display as:
1,1,1=
1,1,2=

It's perfect!!! What I need to do now and would like to use a form for is after that first step is done. I would like a prompt or whatever that says:
Agency 1(reading from A1), Branch 1(B1), Department (C1). What would you like to change this to?

Here the user would be given three empty boxes to indicate 1,1,1 will now be 7,2,3

In the end I would like my output to be something like
1,1,1=7,2,3
I guess the prompt would need to cycle through all the combinations in A,B,C until all are assigned a new "code".


Got any ideas for that.

Before moving on to the 2nd part of your problem

Try this macro to see if it resolves the 1st part of your problem

You will need both the sub BuildList & Function bValidInput codes

Sub BuildList()

Dim iAgency As Integer
Dim iBranch As Integer
Dim iDep As Integer
Dim iRow As Integer
Dim sReq(1 To 3) As String

'Prepare sheet
Cells.ClearContents
Range("a1:c1").Value = Array("Agency", "Branch", "Department")

iRow = 1
sReq(1) = InputBox("Set Up For How Many Agencies?")
If bValidInput(sReq(1)) = False Then
Exit Sub
End If

For iAgency = 1 To Val(sReq(1)) Step 1
sReq(2) = InputBox("Set Up Agency " & iAgency _
& " For How Many Branch's?")
If bValidInput(sReq(1)) = False Then
Exit Sub
End If
For iBranch = 1 To Val(sReq(2)) Step 1
sReq(3) = InputBox("Set Up Agency " & iAgency _
& Chr(10) & "Branch " & iBranch _
& Chr(10) & "for How Many Department's?")

If bValidInput(sReq(1)) = False Then
Exit Sub
End If

' add list to sheet
For iDep = 1 To Val(sReq(3)) Step 1
iRow = iRow + 1
If iAgency <= 9 Then
Cells(iRow, "a").Value = iAgency
Else
Cells(iRow, "a").Value = Chr(iAgency + (64 - 9))
End If

If iBranch <= 9 Then
Cells(iRow, "b").Value = iBranch
Else
Cells(iRow, "b").Value = Chr(iBranch + (64 - 9))
End If

If iDep <= 9 Then
Cells(iRow, "c").Value = iDep
Else
Cells(iRow, "c").Value = Chr(iDep + (64 - 9))
End If
Next iDep
Next iBranch
Next iAgency
End Sub

Function bValidInput(sVal As String) As Boolean
bValidInput = False
Select Case sVal
Case "", 0
Exit Function
End Select
If Not IsNumeric(sVal) Then
Exit Function
End If
bValidInput = True
End Function

Mostly because of you, I have successfully completed the first part. Things changed slightly. I'm running a User form that get the information and puts it in sheet1. There is a button that the user hits when all the agencies have been entered. Once they hit that button all "calculations" are done and inputted on sheet2, in columns a,b,c....agency, branch, department respectively. I also made it were a "=" was added after each department. That way when I send the file to .csv format the output is:
1,1,1=
1,1,2=
....and so on.

It's perfect!....so far.

The other half of the eventual .csv file will include new information. For example:
1,1,1=8,2,4
1,1,2=6,3,1

Once I have the .csv file in that format....I'm done!

So, since I am making this so the user can only see the userform I created, this is what I'm looking to do.....
Once the button that indicated they are finished entering all agency information is hit, I would like another form to pop up that pulls in the information row by row.....For example.....let's say the sheet2 that got created had three rows:
1,1,1=
1,1,2=
1,2,1=
I want a form that can say something like:
Agency 1, Branch 1, Department 1. What would you like to change this to?

Then I would like three MORE text boxes for the new codes. Then I guess a next button, that when hit saves the new codes. I would want these codes to go in columns 4,5,6.

So sheet2 (or maybe sheet3), not sure yet would eventually look like this:
1|1|1=|4|5|6
1|1|2=|2|4|1

The second part seems tricky to me, but I'll start it and please offer any suggestions you can. Like I would like an indicator to let the user know that the last row of data was reached and their .csv file was created.


Again, I can't thank you enough.

I dont quite get where the 2nd half of the equation thing comes from?

Are you saying for each combination you want it to say what you would like to change it to? So you could have:
1,1,1=
1,1,2=
1,2,1=
1,2,2=
and the user could specify what numbers to put against each? So it could end up as:
1,1,1=8,4,1
1,1,2=5,4,3
1,2,1=7,8,4
1,2,2=8,7,8

?

Also you said you wanted each number in its own cell? And does the equals sign require its own cell? Finally you want the whole sheet this is formed on to be exported as a csv?

Michael.

Yes, for every possible combination:
1,1,1
1,1,2.....so on, the user will specify three new numbers. So the only way to do this is to manually enter the new info for each one. I want a second userform to some up that pulls in the three numbers for the current and then gives the user three empty coluns for the new. These three values would then be added to the sheet.
I added the equal sign in at the end of the column C field....that way when transferred to csv form,which I've already figured out, a comma is not placed before the = sign.
Ideally, I would want the data placed in the sheet as follows
|Original Agency Number|Original Branch Number|Original Dept. NUmber=New Agency Number|New Branch Number|New Department number.

If it's in this format in Excel it will transfer over to .csv like this:
1,2,3=4,2,3

That's perfect. It will go to another program that will change the actual structure.

So again, I have Sheet2 with the orignal data, plus the = sign at the end of column C...now I need to get the rest of that filled in.