Any way to index-match across rows and columns?

Question

I'm struggling with conversion from one table format to another. Namely, my source data looks like this:

		Month	1	1	2	2
Year	Country	Category	Export	Import	Export	Import
2008	France	1	19298	25638	21354	19626
2008	France	2	2675	5359	3018	24423
2009	France	1	246781	175638	203479	174495
2009	France	2	96857	47429	104788	247950
2008	Italy	1	230999	133560	248414	147667
2008	Italy	2	235473	197860	258523	224402
2009	Italy	1	38453	5299	45022	6383
2009	Italy	2	88823	41088	93526	43115

but that's not usable.

I want to transform the table to look like this:

Year	Month	Partner	Category	Type	Value
2008	1	France	1	Export	19298
2008	1	France	1	Import	25638
2008	1	France	2	Export	2675
2008	1	France	2	Import	5359
2008	1	Italy	1	Export	230999
2008	1	Italy	1	Import	133560
2008	1	Italy	2	Export	235473
2008	1	Italy	2	Import	197860
2008	2	France	1	Export	21354
2008	2	France	1	Import	19626
2008	2	France	2	Export	3018
2008	2	France	2	Import	24423
2008	2	Italy	1	Export	248414
2008	2	Italy	1	Import	147667
2008	2	Italy	2	Export	258523
2008	2	Italy	2	Import	224402

but I don't know how to make the transformation.

I have tried to index-match but with two conditions I can't process all the values and with multiple conditions scanning rows and columns didn't work.

I have tried SUMIFS, but it requires identical ranges of criteria and summation.

Answer 1

I propose to use the VBA macro for this operation.

Sub Transform()
  Dim cr As Range, cc As Range, i As Integer
  With Sheet2
    Range([a2], [c2]).Copy .Cells(1, 1)
    Cells(1, 3).Copy .Cells(1, 4)
    .Cells(1, 5) = "Operation"
    .Cells(1, 6) = "Quantity"
    i = 2
    For Each cr In Range([a3], [a3].End(xlDown))
      For Each cc In Range([d1], [d1].End(xlToRight))
        Range(cr, cr.Offset(0, 2)).Copy .Cells(i, 1)
        Range(cc, cc.Offset(1, 0)).Copy
        .Cells(i, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone _
          , SkipBlanks:=False, Transpose:=True
        Cells(cr.Row, cc.Column).Copy .Cells(i, 6)
        i = i + 1
      Next
    Next
  End With
End Sub

You don't need to modify it to adjust for numbers of columns and rows.

Run the macro while Sheet1 is active. The result is on Sheet2.

Unpivot user-defined function

Another way is to create and use a user-defined function:

Unpivot(HoleTable, TopLeftDataCell[, ColumnHeader1, ColumnHeader2, ...])

where

• HoleTable - the input table including row and column header (red framed on the picture below)
• TopLeftDataCell - the topmost leftmost cell of data range (green framed on the picture below)
• ColumnHeader1, ColumnHeader2, ... - names for column headers, can be any combination of ranges or values

Function Unpivot(table As Range, topleftvalue As Range, ParamArray headers() As Variant) As Variant
  Dim i As Long, j As Long, k As Long, m As Long, titles As Variant
  Dim rh As Long, ch As Long, output() As Variant
  If table.Columns.Count < 3 Or table.Rows.Count < 3 Then
    Unpivot = CVErr(xlErrNA)
    Exit Function
  End If
  rh = topleftvalue.Column - table.Column
  ch = topleftvalue.Row - table.Row
  If rh < 1 Or rh >= table.Columns.Count Or ch < 1 Or ch >= table.Rows.Count Then
    Unpivot = CVErr(xlErrNA)
    Exit Function
  End If
  titles = headers
  titles = GetArray(ch, titles)
  ReDim output((table.Columns.Count - rh) * (table.Rows.Count - ch), rh + ch) As Variant
  For i = 1 To rh
    output(0, i - 1) = table.Cells(ch, i)
  Next
  For i = 0 To ch
    output(0, rh + i) = titles(i)
  Next
  m = 1
  For i = ch + 1 To table.Rows.Count
    For j = rh + 1 To table.Columns.Count
      For k = 1 To rh
        output(m, k - 1) = table.Cells(i, k)
      Next
      For k = 1 To ch
        output(m, rh + k - 1) = table.Cells(k, j)
      Next
      output(m, rh + ch) = table.Cells(i, j)
      m = m + 1
    Next
  Next
  Unpivot = output
End Function

Function GetArray(dn As Long, sa As Variant) As Variant
  Dim i As Long, j As Long, k As Long, p As Long, m As Long, n As Long, o As Long, r As Long
  Dim v As Variant, va As Variant, ia As Boolean
  ReDim oa(dn) As String
  If (IsEmpty(sa)) Then
    i = -1
  Else
    i = UBound(sa)
  End If
  r = i
  p = 0
  m = 0
  For j = 0 To dn
    ia = True
    While ia
      If p > i Then
        ia = False
      Else
        If IsArray(sa(p)) Then
          If IsEmpty(sa(p)) Then
            p = p + 1
          Else
            If m = 0 Then
              va = sa(p)
              m = UBound(va, 1)
              n = UBound(va, 2)
              r = r + m * n - 1
              k = 1
              o = 1
            End If
            If k <= m And o <= n Then
              v = va(k, o)
              o = o + 1
              If o > n Then
                o = 1
                k = k + 1
              End If
              ia = False
            Else
              m = 0
              p = p + 1
            End If
          End If
        Else
          v = sa(p)
          p = p + 1
          ia = False
        End If
      End If
    Wend
    oa(j) = IIf(j <= r, v, IIf(j = dn, "Value", "Colum" & (j + 1)))
  Next
  GetArray = oa
End Function

'GetArray(N as long, InputArray as variant)' is the supporting function which returns an array of N column names taking them from InputArray as well as values are available in it. If no/not all values provided, the function returns { "Column1", "Column2", ... "Value" } or the tail of this array.

Answer 2

This task can be accomplished quite easily using POWER QUERY as well, which will be the easiest approach. Available in Windows Excel 2010+ and Excel 365 Windows or Mac

---

• First convert the source range into a table and name it accordingly, for this example I have named it as Table1

---

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

---

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Year", "Country", "Category"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Year", "Attribute.1", "Country", "Category", "Attribute.2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute.1", "Month"}, {"Attribute.2", "Type"}})
in
    #"Renamed Columns"

---

---

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

---

Workbook : Download from here