Convert an Excel table from one structure to the other

Question

Original table

I have this table in Excel or LibreOffice.

Unit number	Type	Name
1	Object	Top
1	Object	Bottom
1	Object	Left
1	Object	Right
1	Object	Back
1	Object	Front
1	Property	Right-Fixed
1	Property	Left-Fixed
1	Property	4-legs

New table

I want to convert this table to a new one. I want to keep only the rows with the Type equal to Object and apply the Properties as new columns. Like below.

Unit number	Type	Name	Right-fixed	Left-fixed	4-legs
1	Object	Top
1	Object	Bottom			True
1	Object	Left		True
1	Object	Right	True
1	Object	Back
1	Object	Front

Question

How can I do that in Excel or LibreOffice? My options are:

• Formula-based approaches.
  • Will it get too complex?
• VBA macro programming.
  • Is it overkill for this problem?

I'd appreciate any hint or help.

Note

Above, I have shown just the Unit number of 1 as a sample. But unit numbers could continue, like 2, 3, and more.

Answer 1

• Add a mapping table define the relationship between objects and properties
• The sequence of object names for each unit may different.

Option Explicit
Sub Demo()
    Dim arrData, rngData As Range
    Dim arrRes, iR As Long, i As Long
    Dim LastRow As Long, sHeader As String
    Dim dataSht As Worksheet, mapSht As Worksheet
    Dim oDicMap As Object, oDicCol As Object
    Dim oDicRow As Object, oDic As Object, sKey
    Const BASE_COLS = 3 ' The first 3 cols on output table are fixed
    Set dataSht = Sheets("Sheet1") ' modify as needed
    Set mapSht = Sheets("Sheet2")
    ' Load mapping table
    Set oDicMap = CreateObject("scripting.dictionary")
    arrData = mapSht.Range("A1").CurrentRegion.Value
    For i = LBound(arrData) + 1 To UBound(arrData)
        oDicMap(arrData(i, 1)) = arrData(i, 2)
    Next i
    ' Load source data
    Set oDicCol = CreateObject("scripting.dictionary")
    arrData = dataSht.Range("A1").CurrentRegion.Value
    iR = BASE_COLS
    ' Get header names of output
    For i = LBound(arrData) + 1 To UBound(arrData)
        If arrData(i, 2) = "Property" Then
            iR = iR + 1
            oDicCol(arrData(i, 3)) = iR
        End If
    Next i
    Set oDic = CreateObject("scripting.dictionary")
    Set oDicRow = CreateObject("scripting.dictionary")
    ' Output table header
    ReDim arrRes(1 To UBound(arrData), 1 To iR)
    arrRes(1, 1) = "Unit"
    arrRes(1, 2) = "Type"
    arrRes(1, 3) = "Name"
    For Each sKey In oDicCol.Keys
        arrRes(1, oDicCol(sKey)) = sKey
    Next
    iR = 1
    ' Loop through data
    For i = LBound(arrData) + 1 To UBound(arrData)
        ' Add a new row for Object
        If arrData(i, 2) = "Object" Then
            iR = iR + 1
            arrRes(iR, 1) = arrData(i, 1)
            arrRes(iR, 2) = arrData(i, 2)
            arrRes(iR, 3) = arrData(i, 3)
            sKey = arrData(i, 1) & arrData(i, 3)
            oDicRow(sKey) = iR
        ElseIf arrData(i, 2) = "Property" Then
            ' Insert True
            If oDicMap.exists(arrData(i, 3)) And oDicCol.exists(arrData(i, 3)) Then
                sKey = arrData(i, 1) & oDicMap(arrData(i, 3))
                If oDicRow.exists(sKey) Then
                    arrRes(oDicRow(sKey), oDicCol(arrData(i, 3))) = True
                End If
            End If
        End If
    Next i
    ' Write ouput to sheet
    Sheets.Add
    Range("A1").Resize(iR, UBound(arrRes, 2)).Value = arrRes
    ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub

Answer 2

Formulas should work fine. You can just sort the table by the Type column, and then delete all the rows that don't have 'Object'. Then you just need to add the new columns, i.e. 'Right-fixed', etc. and use some simple formulas to return either "" or 'True', i.e., IF([Name] = "Right-Fixed", "True, "").

Once you have those columns set up and the formulas returning either True or "", you can copy the columns and paste as value, to hard-code the results, and then you can delete the 'Name' column.

Answer 3

Assuming there is no Excel Constraints then the following formula should work as per the tags posted. The following formula is a single input dynamic array formula.

---

=LET(
     _data, A2:C10,
     _object, FILTER(_data,INDEX(_data,,2)="Object"),
     _property, UNIQUE(TOROW(TAKE(FILTER(_data,INDEX(_data,,2)="Property"),,-1)),1),
     _rows, ROWS(_object),
     _cols, COLUMNS(_property),
     _name, SWITCH(LEFT(_property,2),"Ri","Ri","Le","Le","4-","Bo"),
     _convert, HSTACK(_object,MAKEARRAY(_rows,_cols,LAMBDA(r,c,N(INDEX(LEFT(TAKE(_object,,-1),2),r)=INDEX(_name,c))))),
     _headers, HSTACK(A1:C1,_property),
     VSTACK(_headers,_convert))

---

• _data --> variable used for the data range, excluding the headers,
• _object --> Using FILTER() returns the range which consists of only object
• _property --> Using FILTER() returns the range which consists of only property, and then taking the last column of that range with unique values transformed into columns from rows.
• _rows --> Using the ROWS() to get number of object rows count.
• _cols --> Using COLUMNS() to get the number of property columns count.
• _name --> Using the SWITHC() function amending the names so as to match with the properties.
• _convert --> This uses a combination of HSTACK() for one part and MAKEARRAY() for another part. So the MAKEARRAY() is returning an array with n rows and n columns based on the custom LAMBDA() calculation applied, so what it does it just tries to do a BOOLEAN LOGIC and returns 1 for TRUE and 0 for FALSE which is custom formatted as [=1]TRUE;;. While using the HSTACK() we are combining the return value of MAKEARRAY() as well as the variable _object.
• _headers --> as the name defines returning the respective headers for the array.
• Lastly, using VSTACK() to append both the arrays, here _headers with _convert.

---

One small change can be done by removing the _name variable if you are sure about the property labels then,

• Instead of this

N(INDEX(LEFT(TAKE(_object,,-1),2),r)=INDEX(_name,c))

---

• Use this in place of that. remember to remove the _name variable first.

N(INDEX(TAKE(_object,,-1),r)=INDEX(TEXTBEFORE(SUBSTITUTE(_property,"4","Bottom"),"-"),c))

---

Edit:

Here is an updated answer:

---

• Formula used in cell E1

=LET(
     _Data, A1:C19,
     _Mapping, {
         "Right-Fixed", "Right";
         "Left-Fixed", "Left";
         "4-Legs", "Bottom";
         "Front-Fixed", "Front";
         "Back-Fixed", "Back";
         "Folder", "Top"
      },
     _Headers, HSTACK(
         TAKE(_Data, 1),
         TOROW(
             TAKE(_Mapping, , 1)
         )
      ),
     _Object, FILTER(
         _Data,
         INDEX(_Data, , 2) =
             "Object"
     ),
     _Rows, ROWS(_Object),
     _Columns, COLUMNS(
         DROP(_Headers, , 3)
     ),
     _Matched, MAKEARRAY(_Rows,_Columns,LAMBDA(r,c, LET(
     _Property, FILTER(INDEX(_Data,,3),(INDEX(_Object,r,1)=INDEX(_Data,,1))*(INDEX(_Data,,2)="Property")),
     _MappedProps, VLOOKUP(_Property,_Mapping,2,0),
     _MappedObj, VLOOKUP(INDEX(DROP(_Headers,,3),c),_Mapping,2,0),
      N(OR((_MappedProps=_MappedObj)*(_MappedProps=INDEX(_Object,r,3))))))),
     _Merged, HSTACK(
        _Object,
        _Matched
     ),
     VSTACK(_Headers, _Merged)
  )

---