1

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)

1 Answer 1

0

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.

2
  • worked replacing commas with semicolon. ;-) is there a solution without having to copy and past every "if"?
    – Kintaro
    Jan 7, 2017 at 16:57
  • I don't know of a solution without multiple if. Yes, if your locale uses commas for decimal separators, then semicolons separate the arguments of a function. Where a decimal dot is used, the arguments are separated by commas. Something to keep in mind when reading questions and answers on this site, with users from around the world.
    – user135384
    Jan 7, 2017 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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