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.)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
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
10,240
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,431
Messages
5,528,735
Members
409,831
Latest member
KT50

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top