How to prepare a BIMQ Excel file

These tips refer to BIMQ Excel exports that have the following form:

To import these files, three things need to be fixed:

1. Filter for Properties and Specifications

   Add a column and name it e.g. “Filter”. In row 3 in the cell of this column paste the following formula and drag it down in to the end of the table:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in G3
   //F: Column with "Typ"
   
   =WENNS( 
   F3="Eigenschaft"; "Merkmal"; 
   F3="Element"; "Spezifikation"; 
   UND(F3="Gruppe"; NICHT(F4="Eigenschaft")); "Spezifikation"; 
   UND(F3="Gruppe"; F4="Eigenschaft"); "Gruppe" 
   )

   If necessary replace F to reference the column “Typ”.

2. Hierarchy Detection
   Add a column and name it e.g. “Hierarchy Level”. In the cells of this column past the following formula:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in the new column in row 3
   //H: Column with "Filter", A:D: Columns with joined cells for the names of elements, groups and properties
   
   =WENN(H3="Spezifikation";MAX(WENN(A3:D3<>"";SPALTE(A3:D3)));"")

   If necessary replace D to reference the column “Name”.

3. Property Groups
   If you want to import the property groups, add a column and name it e.g. “Property Group”. In row 3 in the cell of this column paste the following formula and drag it down in to the end of the table:

   Example
   [DE]
   [EN]
   Copy
   //this is entered in H3
   //G: Column with "Filter", H: current column with "Property Group"
   
   =WENNS(
   G3="Gruppe"; VERWEIS(2;1/(A3:D3<>"");A3:D3);
   G3="Merkmal";H2;
   G3="Spezifikation";""
   )

   If necessary replace G to reference the column “Filter” from the step before and H to reference the current column.

Tips & Tricks

• If you are not that deep into the Excel functions, try asking an AI chatbot for help. To get good results, tell it to only use the official Excel formulas and the language of your Excel.

• If you are extracting or combining information into a new column, make it manually for 2-3 entries. Then hit Strg + E to auto-fill the rest of the column.