0

I need to convert a table into a list so it can be used as a database

enter image description here

Into this:

enter image description here

0

See Test Sheet cell I2

Formula consists of 4 parts for each column.

ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(",",REPT(A2:A&",", COUNTA($C$1:$G$1))), ","))), 
ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(",",REPT(B2:B&",", COUNTA($C$1:$G$1))), ","))), 

These parts of formula repeat values from columns A and B N times, where N is number of columns with data in your initial table (5 in example), thus you can adjust it to table size changing this part COUNTA($C$1:$G$1).

ARRAYFORMULA(TRANSPOSE(SPLIT(REPT(JOIN(",",$C$1:$G$1&","),COUNTA(A2:A)),",")))

this formula repeats headers of initial table N times, where N is number of rows in your data. Also can be adjusted for different tables by changing $C$1:$G$1 - headers range.

ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(C2:G&","),",")))

Last part transforms values from table into single column. Also can be adjusted for different tables by changing C2:G.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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