Sorry for the imprecise title, I'm not sure what this format is called. I received a "research database" in format you see below and need to convert it (and dozens more like it) into a useable format.

The sheets look like this:

I need to convert it to a properly formatted data sheet so I can upload it to a database. In other words, it needs to become:

It's ok if we end up with rows with no value, those are easy enough to purge.

Sample sheet here

CodePudding user response：

Try this, where tab is your data

=query({
arrayformula(vlookup((roundup(flatten(sequence(columns(tab),rows(tab),1,1)/columns(tab)))),{flatten(sequence(columns(tab),rows(tab),1,1)),flatten(transpose(tab))},2,0)),
arrayformula(vlookup(mod(flatten(sequence(rows(tab),columns(tab),0,1)),columns(tab)) 1,{flatten(sequence(rows(tab),columns(tab),1,1)),flatten(tab)},2,0)),
flatten(tab)
},"select * where Col1 is not null and Col2 is not null and Col3 is not null",0)

CodePudding user response：

First, you'll want to delete what appears to be test data from A9:C of your "Data as Is" sheet.

After that, delete everything you currently have in the results sheet (or create a new blank one) and place the following formula in A1:

=ArrayFormula({"Company","Report","Score";SPLIT(QUERY(FLATTEN(IF('Data as Is'!B2:E="",,'Data as Is'!A2:A&"~"&'Data as Is'!B1:E1&"~"&'Data as Is'!B2:E)),"Select * WHERE Col1 Is Not Null"),"~")})

This will produce your headers and all results, with no-result rows already removed.

From there, you can format headers and data as you like.