Index Generator - Help with Permutations

[Garrett S]

Hello,

I just joined because I need help with an index generator that I am working on.

I have been asked to create an index of all possible combinations of atributes from different sheets. For example, I may have three sheets:

Size Sheet
Small
Medium
Large

Magnitude Sheet
Dark
Dim
Average
Bright

Weight Sheet
Light
Manageable
Heavy

I would need to assign unqiue index numbers to each line item on each sheet (which I have already done), and create a macro which lists every possible combination of the attributes on another sheet (the index). An example index might look like this:

ID Description
01_01_01 Small_Dark_Light
.....
....
03_01_02 Large_Dark_Manageable
.....
.....

and so on, but for ALL possible combinations of the attributes. You should know that I have far more line items than I list in the example here, but the total number of line items should fall below the excel max of 65k.

I would post my current VBA code for his, but I am so sure its completely wrong that I would prefer a fresh start.

Any help that someone could give would be much appreciated.

Regards,

Garrett


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to reference Assemblies in the GAC
http://www.eggheadcafe.com/tutorial...3-4e258f164faa/how-to-reference-assembli.aspx

 

[Bernie Deitrick]

Garrett,

Is it always three sheets?

Let's say that you have your lists in column A of Sheet1, Sheet2, and Sheet3, and those are the only
sheets in your workbook. All else is blank. Run the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeCombos()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet
Dim Sh4 As Worksheet

Set Sh1 = Worksheets(1)
Set Sh2 = Worksheets(2)
Set Sh3 = Worksheets(3)
Set Sh4 = Worksheets.Add(After:=Sheets(3))

For i = 1 To Sh1.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh1.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i
For i = 1 To Sh2.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh2.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i
For i = 1 To Sh3.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh3.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i

myC = 1
For i = 1 To Sh1.Cells(Rows.Count, 1).End(xlUp).Row - 1
For j = 1 To Sh2.Cells(Rows.Count, 1).End(xlUp).Row - 1
For k = 1 To Sh3.Cells(Rows.Count, 1).End(xlUp).Row - 1
myC = myC + 1
Sh4.Cells(myC, 1).Value = "'" & Format(i, "00") & _
"_" & Format(j, "00") & "_" & Format(k, "00")
Sh4.Cells(myC, 2).Value = Sh1.Cells(i + 1, 1).Value & _
"_" & Sh2.Cells(j + 1, 1).Value & "_" & Sh3.Cells(k + 1, 1).Value
Next k
Next j
Next i

End Sub

 

[Garrett S]

Thanks for the quick reply.

There are 5 attribute sheets in total, but I can modify your code, which looks promising!

Appreciate your input, I'll let you know how it works tomorrow.

Regards,

Garrett





Bernie Deitrick wrote:

Garrett,Is it always three sheets?
21-Jan-10

Garrett,

Is it always three sheets?

Let's say that you have your lists in column A of Sheet1, Sheet2, and Sheet3, and those are the only
sheets in your workbook. All else is blank. Run the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeCombos()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet
Dim Sh4 As Worksheet

Set Sh1 = Worksheets(1)
Set Sh2 = Worksheets(2)
Set Sh3 = Worksheets(3)
Set Sh4 = Worksheets.Add(After:=Sheets(3))

For i = 1 To Sh1.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh1.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i
For i = 1 To Sh2.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh2.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i
For i = 1 To Sh3.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sh3.Cells(i + 1, 2).Value = "'" & Format(i, "00")
Next i

myC = 1
For i = 1 To Sh1.Cells(Rows.Count, 1).End(xlUp).Row - 1
For j = 1 To Sh2.Cells(Rows.Count, 1).End(xlUp).Row - 1
For k = 1 To Sh3.Cells(Rows.Count, 1).End(xlUp).Row - 1
myC = myC + 1
Sh4.Cells(myC, 1).Value = "'" & Format(i, "00") & _
"_" & Format(j, "00") & "_" & Format(k, "00")
Sh4.Cells(myC, 2).Value = Sh1.Cells(i + 1, 1).Value & _
"_" & Sh2.Cells(j + 1, 1).Value & "_" & Sh3.Cells(k + 1, 1).Value
Next k
Next j
Next i

End Sub

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Implementing Continuous Scrolling UI Pattern in ASP.NET
http://www.eggheadcafe.com/tutorial...b-5ac2a8e73f34/implementing-continuous-s.aspx

 

[Bernie Deitrick]

It should be easy to work out - if you need help modifying the code, post back. And in any event,
let us know if you succeed or not.

HTH,
Bernie
MS Excel MVP