Go from wide-table format to long-table (dynamic)

Question

I am trying to go from a 'wide-formatted' table see image sample to a 'long-formatted' table see image sample using Google Spreadsheets.

I have created an spreadsheet to re-create the scenario, having this in mind:

• Data is being updated by users on tab 'wide'
• Id's can be added at any time (no Ids can be removed)
• Tags can be replaced, removed or even a new tag can be added (limit is 9 tags)

Test document: https://docs.google.com/spreadsheets/d/1lKAFCON2Bh8n3etGKTEFI3LklGdfMT0wrKm1JyoOHag/edit?usp=sharing

Tab 'wide' has an example of how data is originally formatted. Tab 'long' is the expected output (all id-tags possible unique combinations)

Which is the best approach to get a 'long-table' formatted data that also enables me to capture any update on the original source of data?

Thanks!

Answer 1

try:

={"id"\ "tag"; ARRAYFORMULA(SPLIT(QUERY(FLATTEN(
 IF(wide!B2:Z="";;wide!A2:A&"♦"&wide!B2:Z)); 
 "where Col1 is not null"); "♦"))}