Search code examples
google-sheetsspreadsheetgoogle-query-language

Sort an imported table as a 2 column table in Google Spreadsheets

I have imported a 2 axis column, and I'm trying to convert it in a 2 column table.

The original table has this structure:

Date jan feb mar apr ...
1    a   b   c   d
2    e   f   g   h
3    i   j   k   l
4    m   n   o   p
...

And I want to display the data as:

jan1  a
jan2  e
jan3  i
...
feb1  b
feb2  f
...

The specific database is:

=ImportXML("http://www.sii.cl/pagina/valores/uf/uf2015.htm";"//*[@id='contenido']/table//tr")

I've tried with QUERY(), but couldn't manage to do it.

Solution

  • Try this one:

    ={ArrayFormula(TRANSPOSE(text(SPLIT( CONCATENATE(if(row(B2:E5),B1:E1) &"-"&A2:A5  & "|"),"|"),"mmm-d"))),TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(B2:E5&"-")),"-"))}

    Example file with the formula