CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
confuseddesk
confuseddesk

Reputation: 85

How to add extra text to Query results?

I have a sheet with data that a Google Form is continually adding data to. Once the data is added, there is a subset that I need to have sectioned off to another sheet.

I'm transposing the data so it sorts vertically for each Form's data, and the problem I'm running into is that I want each vertical selection to be individually labeled with the same consistent labels and I don't think I can make CONCATENATE work in this case (I would love to be proven wrong!).

Here's a link to the sheet: https://docs.google.com/spreadsheets/d/1efOQRlKaJlffMnLd8f8oPZxJ57wVFGwKwVY3UW0savc/edit?usp=sharing

Here's the formula I currently have in A1 on the 'Credential Request Form' sheet:

=TRANSPOSE(QUERY('Onboarding Form Responses'!A2:J,"Select F,B,C,D,G,A where A is not null order by A desc"))

This presents the data I want and in the order I want it, but without labels before the data (sorry, "label" might be the wrong word here). I've included an example of how I'd like the data to present in cells A9:A13 on the same sheet.

Is it possible to add those labels without a substantial amount of scripting?

Upvotes: 4

Views: 6769

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

No scripting needed.

You can use a similar formula for your needs:

=TRANSPOSE(query(ARRAYFORMULA({"Employee Name:  "&'Onboarding Form Responses'!B2:B, 
                               "Store Number: "&'Onboarding Form Responses'!F2:F, 
                               "Parking Space: "&'Onboarding Form Responses'!G2:G}), 
                                    "Select Col1, Col2, Col3 where Col1 is not null order by Col1 desc"))

enter image description here

Notice that we use "Employee Name: "&'Onboarding Form Responses'!B2:B etc. for our ranges and then Col1, Col2, etc.

Upvotes: 5

Related Questions