# Formula to convert Google Sheet to YAML sequence with 1st column as keys?

I am looking for a formula to transform a source table like this:

``````- City name    Berlin     New York    Paris
Population     3769495    8175133     2148271
Area           891        783         105
``````

…to a single-column, YAML -ish format, with the first column as labels:

``````- City name: Berlin
Population: 3769495
Area: 891
- City name: New York
Population: 8175133
Area: 783
- City name: Paris
Population: 2148271
Area: 105
``````

Is there something smarter / more efficient than manually copying cell references? I’ll need this to work with a fairly large table.

I fiddled around with Transpose and Offset, but just can’t get it to work.

Thank you.

There's probably a way with a hacky formula, but here is a pragmatic approach.

You can fill three cells below each other (A5:A7 in my sheet) with a formula like this (`B:D` is the source table, you might need `B:ZZ`):

``````=CONCATENATE(A\$1,": ",INDEX(B:D,1,(ROW()-2)/3))
=CONCATENATE("  ",A\$2,": ",INDEX(B:D,2,(ROW()-2)/3))
=CONCATENATE("  ",A\$3,": ",INDEX(B:D,3,(ROW()-2)/3))
``````

and then use the autofill function to fill the other cells: • Thank you, Glorfindel! I had hoped for some magic single-cell trick, but as you say, this is a pragmatic solution, and it works. I have now adjusted the formula for more attributes, added an offset and moved it to a second sheet, so e.g. for 10 columns and 10 attributes, it starts like this: =CONCATENATE(Source!\$A1," ",INDEX(Source!B:K,1,0.9+(ROW())/10)) Jun 5, 2020 at 14:33