Google Sheets - Query Multiple Columns for same Data

Question

We have Employee training data in a Google 'Form Response' spreadsheet which ranges from A1 to JQ1000. This spreadsheet is automatically populated when a Google Form is completed but it is difficult to look at so I want to tidy it up.

The Form Response spreadsheet is populated with the Training Event, the date of the event and the employees who attended that event.
To tidy it up I thought it best to use Transpose&Query but some employees name might appear several times in different columns and different rows.
I don't want to have to write 'Select * WHERE C = 'David Bowie' OR D = 'David Bowie' OR E = 'David Bowie'" and so on out to column JQ.

Can someone help me with a simpler formula?

Answer 1

I know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

The solution is very simple - you don't need any complex formulas or scripts to achieve the collation of this data.

first thing don't work on the actual form responses data sheet get the data to a new sheet (see more below on why not to do this)

Now just make a simple pivot table with the headers that you mentioned above in the rows of the pivot table. Date - training event & employee name. in the date or training event header tick mark 'repeat row headers'

And yes the pivot will auto update as new responses come into the other sheet.

Here's why not to work on the form responses sheet directly:

Working directly on the actual google forms responses sheet is not advisable as Google Forms will overwrite any preexisting data and replace it with the form responses answers - be it text formulas or even cell formatting - there's a simple fix for this - do not work directly on that sheet - just bring that data to a new sheet & then your codes & formulas will work just fine be it scripting code or simple vlookup formulas to collate your data.

I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.

With this solution I will show you - you get two benefits:

1. you can retain any cell formatting you want - this method allows you to do that conveniently

2. and most importantly you can use formulas safely without the fear of the formulas being deleted on each new form response entry - as you are aware google forms creates a new entry on the next available row irrespective if you already prefilled formulas in any other column or rows on that response sheet - this is especially useful if your form collects numerical data that needs to be summed up etc. & needs to be used for pivots & charts in real time

The solution is simple - on a corresponding new sheet just use "=arrayformula" & reference the whole form responses answer sheet data range.

What you get on your new sheet is the exact replication of your form responses data sheet that will autoupdate with each new form response & with the added benefit of using unlimited formatting & unlimited formulas without the hassle of losing the same by google forms overwriting rows & deleting formatting & deleting formulas.