CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user2019
user2019

Reputation: 185

Copy over data with concatenating few columns together

In my google sheets I have two sheets/tabs.

In Sheet 1 I have below columns

A B C D
1 Phone# St Address City Buzzer#
2 123 Street 1 Toronto 564
3 456 Street 2 Cambridge
4 523 Street 1 Guelph
5 412 Street 1 Barrie 985

In my sheet 2 I want to copy all these data as below format. I am trying to copy over column A -> Phone# Column as is and concatenate column B | Column C | Column D columns together with ,. Where if There is Buzzer number I want to append word Buzzer end of the city name otherwise leave it blank.

A B
1 Phone# Complete Address
2 123 Street 1, Toronto, Buzzer 564
3 456 Street 2, Cambridge
4 523 Street 1, Guelph
5 412 Street 1, Barrie, Buzzer 985

And Here is How I have tried to copy over data from Sheet1 to sheet2 and concatenate columns B,C,D together

=Query('Sheet1'!A:D,"Select A and CONCATENATE((IF(D<>"",(CONCATENATE("Buzzer-"," ",D,", ")),"")),B,", ",C,", ",D) where A is not null",1)

Obviously this didn't work. Any suggestions or Help please?

Upvotes: 0

Views: 69

Answers (1)

Alistair McEvoy
Alistair McEvoy

Reputation: 361

I've made a quick sheet with the solution using:

=ARRAYFORMULA(IF(ISBLANK(Sheet1!A2:A12), "",ARRAYFORMULA({Sheet1!A2:A12,ARRAYFORMULA(Sheet1!B2:B12&", "&Sheet1!C2:C12&", "&Sheet1!D2:D12)})))

The sheet:

https://docs.google.com/spreadsheets/d/1E1lBW63CyVJAUmq1okIQgn6-FY82QHt829hpeb0yWxw/edit?usp=sharing

Upvotes: 1

Related Questions