Table to show combinations and results...

[Voldysgonemoldy]

Hi all,

I am not Excel proficient so sorry if this is a dumb question. I’ve spent over an hour trying to find help on Google.

Im trying to create a table with the same data in the column and row headers to show possible combinations and the results that they created. I also need each new combination result to be added to the headers.

For example, if you’re trying to find all possible color combinations and the new color that that combo created, as such...

red. Blue. Yellow
red. X. Purple. Orange
blue. Purple. x. Green
yellow. Orange green. X

But then I would need each newly generated item (green, purple, and orange) to become a new heading in each row and column.

Is there an easy way to do this in Excel? Or any other program that you can think of?

(I’m not actually doing this with colors btw, that would be an obnoxiously large table.)

 

[Col Delane]

Howdy & welcome to the Forum

Almost anything is possible in Excel, but it very much depends on clearly working the "rules" that apply to the situation.

In your case, some more information would help progress towards finding a solution.

1. If you're not actually doing this with colors, what is it that you're tabulating (it may make a difference in the approach)?
2. How and when are the values at the intersections of rows & columns determined and entered - by formula or manually by the user?
3. If by formula, I think any solution would expand exponentially in an endless loop till it crashed your system!
4. Is there a potential limit to far far this table would grow (i.e. how many items in the left-most column?)
5. How and when would you expect the additions to the row & column headers to occur?

 

[Voldysgonemoldy]

Howdy & welcome to the Forum

Almost anything is possible in Excel, but it very much depends on clearly working the "rules" that apply to the situation.

In your case, some more information would help progress towards finding a solution.

1. If you're not actually doing this with colors, what is it that you're tabulating (it may make a difference in the approach)?
2. How and when are the values at the intersections of rows & columns determined and entered - by formula or manually by the user?
3. If by formula, I think any solution would expand exponentially in an endless loop till it crashed your system!
4. Is there a potential limit to far far this table would grow (i.e. how many items in the left-most column?)
5. How and when would you expect the additions to the row & column headers to occur?

1. It’s actually for a game called Doodle God. I’m combining elements to create new ones. Start with earth, air, fire, and water; combining water and fire makes steam, which you can then combine with the other 4 to create new elements, etc. some of them are logical (like earth and fire make lava) but some of them you couldn’t really predict (like water and glass make ice) so it really helps to have a way to keep track of everything.
2. The combinations happen randomly and are manually entered.
3. Yep.
4. The total number of elements is 256 for this stage. There are 4 stages total but each of those will start with different elements and have a new limit.
5. The results from the combinations could happen at anytime. Some elements (like life) will create a lot of new elements while some (like ghost) dead end. So I could create 10 new elements in a row or go 30 attempts without creating anything.

I hope that helps. Thanks for your help!

 

[Eric W]

Maybe this will help. Open a new workbook. In B1:E1 put Earth, Air, Fire, Water. Also put them in A2:A5. Now right click on the sheet tab on the bottom and select View Code. Paste the following code in the window that opens:

Private Sub Worksheet_Change(ByVal target As Range)

    If target.Row = 1 Or target.Column = 1 Or target.Cells.Count > 1 Then Exit Sub
    
        Application.EnableEvents = False
        h1 = Cells(1, target.Column)
        h2 = Cells(target.Row, 1)
        r1 = WorksheetFunction.Match(h1, Range("A1:A257"), 0)
        c1 = WorksheetFunction.Match(h2, Range("A1:IW1"), 0)
        Cells(r1, c1) = target.Value
        If WorksheetFunction.CountIf(Range("A1:A257"), target.Value) = 0 Then
            Cells(Rows.Count, "A").End(xlUp).Offset(1) = target.Value
        End If
        If WorksheetFunction.CountIf(Range("A1:IW1"), target.Value) = 0 Then
            Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = target.Value
        End If
        Application.EnableEvents = True
End Sub

Press Alt-Q to close the VBA editor. Now go to the intersection of Fire and Earth (B4) and enter Lava. Lava should also show up in D2, and be added to the ends of the headers at G1 and A7. This should allow you to enter your combinations and keep track of them. Not much error checking in the code, so you might get some odd results if you enter data outside of the header range. Maximum number of elements is 256.

Hope this helps!

 

[Voldysgonemoldy]

Maybe this will help. Open a new workbook. In B1:E1 put Earth, Air, Fire, Water. Also put them in A2:A5. Now right click on the sheet tab on the bottom and select View Code. Paste the following code in the window that opens:

Private Sub Worksheet_Change(ByVal target As Range)

    If target.Row = 1 Or target.Column = 1 Or target.Cells.Count > 1 Then Exit Sub
    
        Application.EnableEvents = False
        h1 = Cells(1, target.Column)
        h2 = Cells(target.Row, 1)
        r1 = WorksheetFunction.Match(h1, Range("A1:A257"), 0)
        c1 = WorksheetFunction.Match(h2, Range("A1:IW1"), 0)
        Cells(r1, c1) = target.Value
        If WorksheetFunction.CountIf(Range("A1:A257"), target.Value) = 0 Then
            Cells(Rows.Count, "A").End(xlUp).Offset(1) = target.Value
        End If
        If WorksheetFunction.CountIf(Range("A1:IW1"), target.Value) = 0 Then
            Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = target.Value
        End If
        Application.EnableEvents = True
End Sub

Press Alt-Q to close the VBA editor. Now go to the intersection of Fire and Earth (B4) and enter Lava. Lava should also show up in D2, and be added to the ends of the headers at G1 and A7. This should allow you to enter your combinations and keep track of them. Not much error checking in the code, so you might get some odd results if you enter data outside of the header range. Maximum number of elements is 256.

Hope this helps!

It worked perfectly! Thank you so so so much!!!

 

[Eric W]

Happy to help!