Google Sheets – Join Cells and Headers in a Row Filtering Blank Cells

filtergoogle sheets

I would like to know how to filter and join each row adding the header before the values addin a ":" beetween Header and Value and separating them with ";". If the value is blank the header don't have to be selected.


Gender

Female
Man

Category
XYZ

ABC

Weight
45

30

Elasticity
High
Medium


The result need to be:

Features
Category:XYZ;Weight:45;Elasticity:High
Gender:Female;Elasticity:Medium
Gender:Male;Category:ABC;Weigh:30

I'm playing with arrayformula, join and filter functions but I can't figured out how to "loop" to join header with values, maybe I simply don't know what function I have to use.

(If possibile I would like to avoid the use of Google Script)

Best Answer

Assuming your data is in the upper-left corner of a sheet, the following works (linebreaks added for readability):

=arrayformula(
  regexreplace(
   regexreplace(
    if(len(A2:A), A1 & ":" & A2:A, "") 
     & ";" & 
    if(len(B2:B), B1 & ":" & B2:B, "") 
     & ";" & 
    if(len(C2:C), C1 & ":" & C2:C, ""), 
   ";+", ";"), 
  "^;|;$", "")
 )

Each if processes one column of data, prepending values by headers if the value is nonempty. The ; are inserted in between.

The regexreplace perform a cleanup. The first one replaces any sequence of semicolons by a single semicolon. The second removes the semicolon at the beginning or end of string.

Although it's tempting to try join(";", ...) here (which wouldn't require the cleanup), it can't handle a two-dimensional array of data.

Related Topic