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

Are there many properties?

I suggest you try following steps:

• Use formula like =FILTER(A2:C10,B2:B10<>"Property") to list all Type values.

• Use formula like =IFERROR(IF(FIND("Right",C2)=1,"True",""),"") for Right property,
• User formula like =IFERROR(IF(FIND("Left",C2)=1,"True",""),"") for Left property,
• Use formula like =IFERROR(IF(FIND("Bottom",C2)=1,"True",""),"") for 4-legs.

Answer 2

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:

Updated Formula which returns as per the desired output:

---

=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)
     )

---

Answer 3

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

let
    Source = Excel.CurrentWorkbook(){[Name="Table45"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit number", Int64.Type}, {"Type", type text}, {"Name", type text}}),

//Create list of all properties to use in the final expansion
    #"All Properties" = List.Distinct(Table.SelectRows(#"Changed Type", each [Type]="Property")[Name]),

// for multiple units Group on unit
    #"Group Unit" = Table.Group(#"Changed Type",{"Unit number"}, {
        {"Props", (t)=>
            let 

            //List of Objects and Properties for this unit only
                Objects = Table.SelectRows(t, each [Type]="Object"),
                Properties = List.Distinct(Table.SelectRows(t, each [Type]="Property")[Name]),

            //Add Property columns to the Objects in the correct location
            //May need to change the logic of the `AddColumn` function depending on your
            //   actual data
                #"Add Property Columns" = List.Accumulate(
                    Properties,
                    Objects,
                    (s,c)=> Table.AddColumn(s,c, 
                        each if Text.StartsWith(c,[Name]) or (c = "4-legs" and [Name]="Bottom") then true else null, type logical))
            in
                #"Add Property Columns",
                type table[Unit number=Int64.Type, Type=text, Name=text,#"Right-Fixed"=logical, #"Left-Fixed", #"4-legs"=logical]}}),

//Remove unneeded column and expand the subtable
    #"Removed Columns" = Table.RemoveColumns(#"Group Unit",{"Unit number"}),
    #"Expanded Props" = Table.ExpandTableColumn(#"Removed Columns", "Props", 
        Table.ColumnNames(#"Changed Type") & #"All Properties")
in 
    #"Expanded Props"

Original Data
Showing two units

Results