Google-sheets – Scanning a column and partitioning information to other sheets in Google Sheets

formulasgoogle sheetsgoogle-formsgoogle-sheets-query

I am a membership manager in an international organization and I was hoping to get some help optimizing our members' contact list.

There are three kinds of members, NCs, LCs, and IMs. I have a Google Form that allows members to fill out their information dependent on whether they are in an NC, LC, or IM. This pulls their information (country, city, phone, email, etc) into a Google Sheet. In another sheet of the same file, I have it partitioned off; listing the NCs on one sheet, the LCs on, and the IMs on another.

One of the first questions they answer on the form is to select NC, LC, or IM. I want to create the other sheets so that they scan the form data and if it says "NC", then pull that row into the NC form and the same for LC and IM. However, I am not sure how to write a function that will essentially skip the other two membership types and properly fill the three sheets with the cataloged data correctly.

For example:
Form Sheet (updated when someone fills out the contact form)

This would sort the first row into the NC sheet, pulling in all the information. The second would go to the LC sheet, and then the IM sheet for the third row. But if the next line was an NC, I also want the NC sheet to be able to grab the first row, skip the next two rows, and then pull the fourth row into the second row of the NC sheet. If that makes sense.

Best Answer

There are several ways to achieve the desired result. By using Google Sheets built-in formulas you could use FILTER, QUERY or a combination of functions.

I think that the simplest way is by using FILTER.

Add a sheet for NC
On A1 add the following formula (it assumes that the source data is on sheet named Sheet1.
```
=FILTER(Sheet1!A:D,B:B="NC")
```
Repeat the above for each kind of members.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google Sheets – Forms Data Manipulation

Short answer

Add a new sheet.
Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

OFFSET() is used to get the values from the source data.
'Form responses 1'!$A$2 is the start point to count columns and rows.
row() is used to increment the row and column iterators.
ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet