-1

I've searched for a solution to this and have come close, but I can't figure out a final solution. I'm new to queries in Google Sheets. I'll include a link to the sheet and the query I'm using.

I want my query to only return results from cells with a value, so there are not so many empty columns in the sheet. Below is link to my google sheet:

https://docs.google.com/spreadsheets/d/15LME9n4gp_CAi0voUhuaaRrIoUYB3AYA7EuGUoPxY48/edit#gid=1532407141

The formula in the tab "Smith, John" is:

  =query(RESPONSES!A2:BB,"select * where (B = 'Smith, John') 
                                     and (C = 'Smith, John') 
                                     and (D = 'Smith, John')")

You can see it is pulling data from the RESPONSES tab. In the RESPONSES tab, John Smith's scores (input via Google Form) don't start until column AD.

I want John Smith's results, however, to show up on his tab without all of the columns.

So, what do I need to include in the formula to only pull data from cells with values in them (i.e., AD onward).

Thanks for the help.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Josh
  • 163
  • 1
  • 1
  • 7
  • This is easy with Google apps script, are you open to that? – Robin Gertenbach Nov 09 '16 at 21:46
  • Please don't tag Google sheets questions with Excel. – Tim Williams Nov 09 '16 at 22:00
  • Sorry about the mis-tag Tim! – Josh Nov 10 '16 at 15:16
  • @RobinGertenbach Yes, I'd be interested in seeing the Google apps script option. I'm just starting to learn in this area and I'd like to see the best option. – Josh Nov 10 '16 at 15:17
  • It's not clear if you want to show the headers or if you want to remove the columns without responses. – Rubén Nov 10 '16 at 16:38
  • I do want to show the headers. Basically, I'd like it to look Just like RESPONSES, but have individual tabs for each employee's data. (i.e., a "BROWN, LINDA" tab, etc.) – Josh Nov 10 '16 at 16:56
  • I should also add that the number of columns in RESPONSES will grow as more employees take the assessment. So, my guess is that using the range A2:BB will be insufficient. I also guess that a Google apps script will be able to specify a dynamic range (basically everything in the sheet). Thanks again for all of the help. Like I said, I'm a complete newbie at this, but want to learn it well. – Josh Nov 10 '16 at 17:08

1 Answers1

-1

You can add one more sheet to accomplish this.

Sheet1 (additional) has the formula in cell A1:

=query(RESPONSES!A1:BB,"select * where (B = 'Smith, John') and (C = 'Smith, John') and (D = 'Smith, John')")

The same as your formula, but includes headers.

Your sheet formula is:

=FILTER(OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A),COLUMNS(Sheet1!1:1)),Sheet1!2:2<>"")

It takes data from Sheet1 and filters it to exclude empty rows.

See sample sheet

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • Thanks Max! This works. Still interested in the Google apps script version, mentioned above. I appreciate your help. It would be great if I didn't have to create an extra sheet, though. Any way to do that with a formula? – Josh Nov 10 '16 at 15:28
  • You have so called "wide" table. This is not the way to go. Please, read [this article](https://github.com/RobinGertenbach/Reshape), written by @robin-gertenbach – Max Makhrov Nov 11 '16 at 08:41
  • My sheet might be a bit deceptive in that the CC1 - KPI1 will actually be different for different users. They align to Core Competencies and Key Performance Indicators for individual employees. – Josh Nov 16 '16 at 17:57