Convert cells to table

Question

I have this spreadsheet:

Cell 1A: key a: 111; key b:Abc ;key c: xyz
Cell 1B: key c: 55t; key b:mmm ;key a: 123

Need help or pointers to find a way to build from it table on another sheet that looks like this:

key a | key b | key c
 111  |   Abc | xyz
 123  | mmm   | 55t

Is that doable with no code, just configuration?

Answer 1

Assuming per tag listed there is no excel version constraint, you can use the following formula in C1:

=LET(cols, INDEX(TRANSPOSE(TEXTSPLIT(A1,":",";")),1,), n, COLUMNS(cols),
  REDUCE(cols, A1:A2, LAMBDA(ac, a, LET(arr, TRANSPOSE(TEXTSPLIT(a,":",";")), 
   aCols, INDEX(arr,1,), by_arr, XLOOKUP(aCols, cols, SEQUENCE(1,n)),
    VSTACK(ac, SORTBY(INDEX(arr,2,), by_arr))))))

Here is the output:

Explanation

We use TEXTSPLIT to convert to an array the cell information. For example for the first cell, the following formula:

=TRANSPOSE(TEXTSPLIT(A1,":",";"))

produces the following output:

key a    key b  key c
111      Abc    xyz

Via INDEX function we get the first row of previous array and name it cols. It represents the column names. Since for the following input rows the information can appear in different order, we use cols as a reference to find the permutation required on each iteration to get the information in the correct order. We use for that SORTBY function.

We use REDUCE/VSTACK pattern. Check for example: how to transform a table in Excel from vertical to horizontal but with different length to add on each iteration a new row via VSTACK with the values in the correct order.

On each iteration (a) for the input range A1:A2 we convert the information into 2D array (arr), where the first row is the header (aCol) and the second row the corresponding values (INDEX(arr,2,)).

The output of XLOOKUP (by_arr) has the corresponding permutation of the columns (aCol) with the respect to the first column cols. Then finally VSTACK adds on each iteration the values in the correct order.