Transpose values from a row where criteria are met

Question

I'm trying to analyse the discount offered by various companies for a certain train journey and list the company name and discount accordingly.

The input dataset looks like the following image:

What I want to do is essentially transpose the dataset and create the following output where the discount is > 0 and return the company/column name. (see below)

I have tried to use the formulas below:

{=INDEX(A1:F7,SMALL(IF(AND(A2:A7=H2,B2:F2>0),COLUMNS(B1:F1)),COLUMNS(1:1))-1,3)} 

- This seems to always yield a #NUM! error

=INDEX(A1:F7, MATCH(H2,A2:A7,0),MATCH(I2,B1:F1,0))

• this only works if I have listed out all the possible routes and have one route for each company, even if the discount is 0. Given that I have 40 companies and 15000+ routes, I don't really want to be creating a long list of LONPAR with each company in column B followed by LONFRA etc.

Is there anything I'm missing, or any guidance, or functions you could offer to help me get the solution?

Answer 1

The following code seems to split out your train discount table fairly efficiently.

Option Explicit

Sub qwewretq()
    Dim a As Long, b As Long, h As Long, aDISs As Variant

    With Worksheets("Sheet10")
        With .Cells(1, 1).CurrentRegion
            aDISs = .Cells.Value2
            h = .Columns.Count + 2
        End With
        .Cells(1, h).CurrentRegion.ClearContents
        .Cells(1, h).Resize(1, 3) = Array("route", "company", "discount")
        For a = 2 To UBound(aDISs, 1)
            For b = 2 To UBound(aDISs, 2)
                If aDISs(a, b) > 0 Then
                    If IsError(Application.Match(aDISs(a, 1), .Columns(h), 0)) Then
                        .Cells(Rows.Count, h + 1).End(xlUp).Offset(1, -1) = aDISs(a, 1)
                    End If
                    .Cells(Rows.Count, h + 1).End(xlUp).Offset(1, 0).Resize(1, 2) = _
                        Array(aDISs(1, b), aDISs(a, b))
                End If
            Next b
        Next a
    End With
End Sub

There are areas that could be tweaked for additional efficiency, notably application environment settings like .ScreenUpdating property and .EnableEvents property. For extremely large blocks of data, building a target array and dumping the resolved data back to the worksheet en masse could also help. You should be able to format the target table for percentages and any visual beutification fairly easily.