0
votes

Background

I am making a Database of organiser names, which i want to update with a Google Form. I already made a Form which feeds a Spreadsheet "Form Responses" sheet with the organiser details: Continent, Country and Chapter/City. The Database is already existing with current organisers, split into continent specific sheets, so i need to add new organisers from the form to this database. If a new organiser is from a new country/chapter, that needs to be added to the database as well.

Question

What formula can I use to feed the "Form Responses" sheet rows into the "Database" sheets, without having multiple rows of the same Country and Chapter? That is, add the "Organiser" column to an existing "Country" + "Chapter", or add the new "Country" + "Chapter" + "Organiser" to the bottom of the correct "Continent_Database".

Maybe the only way to do this might be to add all new rows from the "Form Responses" sheet to the bottom of the correct "Continent_Database", and merge the rows in the case of already existing Country + Chapter, in an additional sheet.

Example

Spreadsheet example with some test Form Responses and a "Africa_Database" example sheet:

https://docs.google.com/spreadsheets/d/1QaDNQp-HrbueQchmlXGSr7cFZ9r2x7ml148vSJL4Q0w/edit?usp=sharing

1

1 Answers

0
votes

You could be looking to use a query here. In the case below 'A2' points a cell containing a country name. Transpose will convert a downward list to a horizontal and the query will get all names from column G where column C contains that the country in cell 'A2'

=transpose(QUERY('Form Respones'!B:G,"SELECT G WHERE C ='"&A2&"'",0))

Here is an example