1

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.

Improve this question

1 Answer 1

Reset to default
0

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:

enter image description here

Improve this answer
1
  • 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))
    – David Winter
    Jun 5, 2020 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.