Help me kill an IF statement with 122 sub-ifs !!!

[spyrule]

Hello,

So I've got an interesting problem, and I'm debating on the best way to kill this bird.

I've got an excel sheet that contains the following (only it continues for 122 columns wide, and 80,000+ rows....):


[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]sku[/TD]
[TD]description[/TD]
[TD]ACURA[/TD]
[TD]INTEGRA[/TD]
[TD]TSX[/TD]
[TD]YEAR[/TD]
[TD]HONDA[/TD]
[TD]CIVIC[/TD]
[TD]ACCORD[/TD]
[TD]....[/TD]
[/TR]
[TR]
[TD]abc123[/TD]
[TD]94-01 Acura Integra, 94-97 Honda Accord, 88-91 Honda Civic CRX, 92-95 Honda Civic, 96-00 Honda Civic 4-2-1 Ceramic "K Series Swap" Header[/TD]
[TD]ACURA[/TD]
[TD]INTEGRA[/TD]
[TD][/TD]
[TD]94-01[/TD]
[TD]HONDA[/TD]
[TD]CIVIC[/TD]
[TD]ACCORD[/TD]
[TD]94-97[/TD]
[/TR]
</tbody>[/TABLE]

• Each Brand "Group" has been grouped and given a named range. For example Column C:F has been called "ACURAList".
• each named range starts with the brand name and ends with YEAR.
• Each YEAR cell is populated with the FIRST INSTANCE of a year range that is found ONLY (vast majority of our products only contain 1 year range)

What I'm trying to do is process through each one of these row, and generate an output on another specific tab called "YMMList".

This list will contain only the following:
[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD]MAKE[/TD]
[TD]MODEL[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]ACURA[/TD]
[TD]INTEGRA[/TD]
[TD]97-01[/TD]
[/TR]
[TR]
[TD]HONDA[/TD]
[TD]ACCORD[/TD]
[TD]94-97[/TD]
[/TR]
[TR]
[TD]HONDA[/TD]
[TD]CIVIC[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Does anybody have a good/sample macro that can walk through each row, column group, by column group and process the output as shown?


Any, and ALL help is appreciated.

 

[Beyond_avarice]

I know I have way too many variables but it's the end of the day and this seems to work. You will need to fill in the Sheet name that contains these Groups.

Sub YMMList()

Dim lRow As Long        'For the initial sheet
Dim YMMlRow As Long     'For identifying the append row of the output sheet
Dim i As Long           'Iterator for Groups, Start at column 3 and iterate in +4 increments
Dim lCol As Long        'Number of columns on initial sheet, assumes multiples of 4
Dim mRow As Long        'Iteration of rows in each Group
 
lCol = Sheets("insert initial sheet name").Cells(2, Columns.Count).End(xlToLeft).Column

For i = 3 To lCol Step 4
    lRow = Sheets("insert initial sheet name").Cells(Rows.Count, i).End(xlUp).Row
    
        For mCol = 2 To lRow
            YMMlRow = Sheets("YMMList").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
            
            Sheets("YMMList").Range(Cells(YMMlRow, 1).Address & ":" & Cells(YMMlRow, 2).Address).Value = _
            Sheets("insert initial sheet name").Range(Cells(mCol, i).Address & ":" & Cells(mCol, i + 1).Address).Value
            Sheets("YMMList").Range(Cells(YMMlRow, 3).Address).Value = Sheets("insert initial sheet name").Range(Cells(mCol, i + 3).Address).Value
            
        Next mCol
        
Next i

End Sub