CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Shaik Naveed
Shaik Naveed

Reputation: 448

Concatenating merged cells with google sheets

I have a use case where I need to concatenate values from merged cell with other columns based on condition

Name Frequency Old Measure New Measure What's needed
Name1 Freq1 Mea1 Name1-Freq1-Mea1
Freq2 Nmea1 Name1-Freq2-Nmea1
Freq3 Mea2 Name1-Freq3-Mea2
Name2 Freq4 Mea3 Name2-Freq4-Mea3
Freq5 Nmea2 Name2-Freq5-Nmea2
Name3 Freq6 Mea4 Name3-Freq6-Mea4
Name4 Freq7 Nmea3 Name4-Freq7-Nmea3
Name5 Freq8 Nmea4 Name5-Freq8-Nmea4
Freq9 Nmea5 Name5-Freq9-Nmea5

The formula should check for column Old Measure and New Measure which ever is filled should concatenate with Name and Frequency.

I did try to take the answer from this similar question - Concatenate merged cells with Google Sheets

and added the check for Old Measure and New Measure column as below but not giving correct results.

=IFS(C1<>"", TEXTJOIN("-",false,INDEX(A:A,LARGE((ISBLANK(A:A)=FALSE)*(ROW(A:A)<=ROW())*ROW(A:A),1)),B1,C1) , D1<>"", TEXTJOIN("-",false,INDEX(A:A,LARGE((ISBLANK(A:A)=FALSE)*(ROW(A:A)<=ROW())*ROW(A:A),1)),B1,D1))

Upvotes: 0

Views: 103

Answers (1)

z..
z..

Reputation: 13111

Here's a possible solution:

=ArrayFormula(query(byrow(
{lookup(row(A2:A),row(A2:A)/(A2:A<>""),A2:A),B2:D},
lambda(r,textjoin("-",1,r))),
"limit "&-1+max(if(B2:D<>"",row(B2:D)))))

enter image description here

Explanation

This part

lookup(row(A2:A),row(A2:A)/(A2:A<>""),A2:A)

Fills the gaps in A2:A with the last non-empty value above.

Then

byrow(...,lambda(r,textjoin("-",1,r)))

Concatenates row-by-row the values from the previous array and the values in B2:D.

And finally

query(...,"limit "&-1+max(if(B2:D<>"",row(B2:D)))

Constrains the resulting array to the last filled row.

Upvotes: 2

Related Questions