Table to show combinations and results...

Voldysgonemoldy

New Member
Joined
Mar 3, 2019
Messages
3
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.)
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
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

New Member
Joined
Mar 3, 2019
Messages
3
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,224
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:

Code:
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

New Member
Joined
Mar 3, 2019
Messages
3
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:

Code:
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!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,650
Messages
5,838,566
Members
430,556
Latest member
Peachforyou

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top