Creating a list of available options from a "table"

Question

Firstly-- I'm working on a sheet that deals with Flutes and the available options for those flutes.

I'd like to create a list of all the available options based on the availability of that option as determined by the "table" I've created.

To elaborate: A model "EX" flute can come with Options "I" or "O" and beyond that, Options "E" and/or "THJ". If I work everything out by hand, this gives me Model #'s EX-I, EX-I-E, EX-I-THJ, EX-I-E-THJ, EX-O, EX-O-E... ETC.

Model GX has more available options, and thus this would create more model numbers...

I've created a table of the available options, and labeled them as either being unavailable on that particular model (N/A) or with the price that the option would add to the base price of the model. Ideally I'd like to not only create the model numbers "dynamically, but also create the pricing dynamically as well.

I've tried "concatenate" but I may as well type them all in by hand. I've also looked at "Textjoin" but that doesn't take into account whether or not an option is available...

Is there a formula I can use to accomplish this? (I have limited knowledge of VBA, but if that's what I need to use, then I'm happy to learn!)

Here's a link to a copy of the file that I'm working on:

https://docs.google.com/spreadsheets/d/1-0fWJ-ity8EUYZH9_6r7rMR-D2lS96bb/edit?usp=sharing&ouid=102555678210407815688&rtpof=true&sd=true

EDIT: I've added my sheet in table format below: (I should note-- If I need to organize my data differently, I'd be glad to do so.)

		Model	EX	GX	DS	SR	PlatinumClad	9kGold	14kGold
		Base Price	4700	7100	9800	13500	15750	20750	29500
Style Suffix (I or O)
	I		0	0	0	0	0	0	0
	O		0	0	0	0	0	0	0
Options Suffixes
	HW		N/A	450	450	450	500	2150	N/A
	E		450	450	800	800	950	1000	1000
	C#		N/A	550	1100	1200	0	0	0
	THJ		400	400	400	400	650	650	650
	ENGRHJ		N/A	N/A	N/A	N/A	900	1800	1800
	ENGR		N/A	N/A	N/A	N/A	2500	3200	3200

Answer 1

Permutations With Sum

• Pay attention to the values in the constants section. They are adjusted to the setup in the workbook whose link you posted.
• The code is to be copied into a standard module, e.g. Module1 (Insert > Module). Press F5 or click on the 'Play' button to run the procedure.

Option Explicit

Sub ListModels()
    
    ' Source
    Const sName As String = "Sheet1"
    Const fvCol As String = "D"
    ' Source Model
    Const mfcAddress As String = "D1"
    ' Source Style
    Const stfcAddress As String = "B4"
    Const sDelimiter As String = "-"
    ' Source Options
    Const otfcAddress As String = "B7"
    Const oDelimiter As String = ","
    ' Destination
    Const dName As String = "Sheet2"
    Const dfcAddress As String = "A1"
    Const dTitle1 As String = "Flute Model"
    Const dTitle2 As String = "Price"
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    
    Dim cCount As Long
    
    ' Model
    Dim mrg As Range
    With sws.Range(mfcAddress)
        Dim mlCell As Range: Set mlCell = _
            .Resize(, sws.Columns.Count - .Column + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If mlCell Is Nothing Then Exit Sub ' no data
        cCount = mlCell.Column - .Column + 1
        Set mrg = .Resize(2, cCount)
    End With
    'Debug.Print mrg.Address
    
    ' Style
    Dim strg As Range
    With sws.Range(stfcAddress)
        Set strg = sws.Range(.Cells, .End(xlDown))
    End With
    Dim svrg As Range: Set svrg = strg.EntireRow.Columns(fvCol).Resize(, cCount)
    'Debug.Print strg.Address, svrg.Address
    
    ' Options
    Dim otrg As Range
    With sws.Range(otfcAddress)
        Set otrg = sws.Range(.Cells, .End(xlDown))
    End With
    Dim ovrg As Range: Set ovrg = otrg.EntireRow.Columns(fvCol).Resize(, cCount)
    'Debug.Print otrg.Address, ovrg.Address
 
    ' Destination
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dfCell As Range: Set dfCell = dws.Range(dfcAddress)
    ' Write title.
    dfCell.Value = dTitle1: dfCell.Offset(, 1) = dTitle2
    Set dfCell = dfCell.Offset(1)
 
    Dim mCell As Range, mTitle As String, mPrice As Double
    Dim sCell As Range, sTitle As String, sPrice As Double
    Dim oCell As Range, oTitle As String, oPrice As Double
    
    ' Model
    For Each mCell In mrg.Rows(1).Cells
        mTitle = mCell.Value
        mPrice = mCell.Offset(1).Value
        ' Style
        For Each sCell In strg.Cells
            sTitle = mTitle & sDelimiter & sCell.Value
            sPrice = mPrice + sCell.EntireRow.Columns(mCell.Column).Value
            ' Write
            dfCell.Value = sTitle: dfCell.Offset(, 1).Value = sPrice
            Set dfCell = dfCell.Offset(1) ' next row
            ' Options
            For Each oCell In otrg.Cells
                oTitle = sTitle & oDelimiter & oCell.Value
                With oCell.EntireRow.Columns(mCell.Column)
                    If IsNumeric(.Cells) Then
                        oPrice = mPrice + .Value
                        ' Write.
                        dfCell.Value = oTitle: dfCell.Offset(, 1).Value = oPrice
                        Set dfCell = dfCell.Offset(1) ' next row
                    'Else ' N/A
                    End If
                End With
            Next oCell
        Next sCell
    Next mCell
    
    With dfCell ' clear below
        .Resize(dws.Rows.Count - .Row + 1, 2).Clear
    End With
    
    MsgBox "List created.", vbInformation
    
End Sub