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

Question

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.

Answer 1

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.

1. Add a sheet for NC

2. On A1 add the following formula (it assumes that the source data is on sheet named Sheet1.

   =FILTER(Sheet1!A:D,B:B="NC")
   

3. Repeat the above for each kind of members.

Answer 2

try this demo spreadsheet if it fits you: https://docs.google.com/spreadsheets/d/

used formula is:

=QUERY('Sheet1'!A1:Z; "select * where B = '"&"NC"&"'"; 1)

where the range is A1:Z of the sheet named Sheet1 and you are importing data from all columns select * and you are looking only for cases where NC is in B column hence: where B = '"&"NC"&"'" and then finally you set 1 header row to be copied with 1