GPT-4 Conversation #1095928049679212544

[deep depot]

I need a Google Sheets formula. This formula should copy the data in Column C of the 'Roster' tab, concatenate the letter "H" at the end of each entry, and also copy the data from Column K in 'Roster', but only the rows in which the value in Column A is "Yes"

[sly agate]

You can use the following Google Sheets formula using the QUERY and ARRAYFORMULA functions:

=ARRAYFORMULA(QUERY({'Roster'!A:C, 'Roster'!C&"H", 'Roster'!K}, "SELECT Col4, Col5 WHERE Col1 = 'Yes'", 0))

This formula will create a resulting array with two columns. The first column will contain the data from Column C in 'Roster' with an "H" at the end, and the second column will have the data from Column K in 'Roster' where Column A has the value "Yes".

[deep depot]

Formula parse error

[sly agate]

I apologize for the mistake. Please try the following Google Sheets formula using IF and ARRAYFORMULA functions:

=ARRAYFORMULA(IF('Roster'!A:A="Yes", 'Roster'!C:C&"H"&", "&'Roster'!K:K, ""))

This formula will check if Column A in 'Roster' has the value "Yes", then concatenate the data from Column C with "H" at the end, and append the data from Column K in a single cell separated by a comma. If Column A doesn't have "Yes", the formula will return an empty cell. The new formula should work without any parse errors

[deep depot]

It puts everything in one column and not two, and I need it to skip blank rows

[sly agate]

I apologize for the confusion earlier. To achieve the desired output, please use the following QUERY and ARRAYFORMULA:

Place this formula in a column where you'd like the data from Column C in 'Roster' with "H" appended:

=QUERY(ARRAYFORMULA(IF('Roster'!A:A="Yes", 'Roster'!C:C&"H", "")), "where Col1 <> ''", 0)

And use this formula in the column where you'd like the data from Column K in 'Roster':

=QUERY(ARRAYFORMULA(IF('Roster'!A:A="Yes", 'Roster'!K:K, "")), "where Col1 <> ''", 0)

The first formula will output the values from Column C in 'Roster' with an "H" added to the end, and the second one will output the values from Column K in 'Roster'. Both formulas will only use the rows where the value in Column A is "Yes" and will skip blank rows.

[deep depot]

That works, oh Deity. You are magnificent.