4

I am trying to create a formula that pulls responses from a Google Form and organizes them into rows correlating with a client's name. The name is entered manually in and also appears in a drop-down in the form, so they should always match up.

I used =ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,'Form Responses 1'!$B$2:$F, B2,FALSE ),"")), where:

  • manually entered names are in column A
  • the column I want results from is in row 2
  • and the data being pulled is coming from sheet 'Form Responses 1'

In the sample, I have highlighted the data in the Form Responses that I am trying to get to show up for each name in the 'Current List' sheet.

Sample Sheet here

  • 1
    I would love to link it to the timestamp so that the latest entries replace any older entries but blank cells do not replace earlier entries. – zprezo Sep 20 '17 at 21:19
2

Try this:B3:

=ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,SORT(FILTER({'Form Responses 1'!$B$2:$F$7,'Form Responses 1'!$A$2:$A$7},'Form Responses 1'!C2:C7<>""),6,0),B2,0)))

Drag fill to the right.

It looks big only because of the name of your sheet :( Otherwise, It's a simple blank filter and sorting by A( timestamp)

Edit: Formula simplified

2

I don't see an arrayformula solution for this. Here is a solution that requires one formula per cell on "Current List" sheet.

To look up contact phone numbers (column B on Current List):

=iferror(query('Form Responses 1'!$A:$F, "select C where B='" & $A3 & "' and C<>'' order by A desc limit 1 label C ''", 1))

The key part is the query string, which is repeated here for readability:

select C 
  where B = '" & $A3 & "' 
  and C <> '' 
  order by A desc 
  limit 1 
  label C ''

So, we select the entry from C where

  • B matches the name in A3 of current sheet
  • C is not empty
  • The entry is the latest (order by decreasing timestamp, take the top result)

Labeling the result as empty string '' means we get single-cell output, as desired. The last parameter of query is the number of header rows in the source data.

The above formula can be copied down the row, so you get contact phone numbers for everyone.

For other text fields (address, pizza preference) the formula is adjusted by replacing C by another letter (D or E) in three places.

For the last field, which is not a text but a date, also replace the non-blank condition C<>'' with F is not null. (The former is used for text, the latter for numbers and dates).

For reference, this is the input data on Form Responses 1:

+--------------------+---------+--------------+-------------+--------------------+----------------------+
|     Timestamp      | Client  |   Contact    |   Address   | Do you like pizza? | Party Date Preferred |
+--------------------+---------+--------------+-------------+--------------------+----------------------+
| 9/20/2017 13:06:36 | Joe     | 555-555-5555 | 123 Main st | Yes                | 9/30/2017            |
| 9/20/2017 13:07:00 | Bob     | 555-111-1111 |             | No                 | 10/10/2017           |
| 9/20/2017 13:07:17 | Joe     |              |             | No                 | 10/27/2017           |
| 9/20/2017 13:07:43 | Jessica | 555-222-2222 | 700 Main St | Maybe              |                      |
| 9/20/2017 13:07:51 | Jessica |              |             |                    | 9/29/2017            |
| 9/20/2017 13:08:50 | Jessica | 555-123-1234 |             |                    |                      |
+--------------------+---------+--------------+-------------+--------------------+----------------------+

and this is the output:

+---------+--------------+-------------+--------------------+----------------------+
|         |   Contact    |   Address   | Do you like pizza? | Party Date Preferred |
+---------+--------------+-------------+--------------------+----------------------+
| Bob     | 555-111-1111 |             | No                 | 10/10/2017           |
| Jessica | 555-123-1234 | 700 Main St | Maybe              | 9/29/2017            |
| Joe     | 555-555-5555 | 123 Main st | No                 | 10/27/2017           |
+---------+--------------+-------------+--------------------+----------------------+
  • This should be the correct answer! It's a very common problem and this is the first solution I've seen that does not involve taking the plunge into Apps Script and it's powerful but ridiculously convoluted environment. The solution to this pattern (automatically expanding formulas to newly inserted rows) involves installable triggers and reading event objects, caching and looping bidimensional arrays to avoid hitting execution time limits. Hardly an endeavor for newcomers to Apps Script. – hlecuanda Sep 30 '17 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.