CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jodi Roney
Jodi Roney

Reputation: 11

How to split a master sheet of email addresses

Ok so the need - I have about 3700 lines of email addresses, names, schools, and professions(those are column headers) I want to split this sheet into 4 with 1000 lines(I understand one will be short) in each but here is the catch I can only have 25 lines/emails from each school. So how would someone go about doing this? Keep in mind each sheet needs to have its own unique emails not repeated on the other sheets.

Upvotes: 1

Views: 161

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

There are 2 problems here and as I don't know how many schools are on the list and if it's possible to have always less than 25 people from one school (for example - if there are only 30 schools, it would be impossible to distribute them in 1000 row batches).

First task: Distribute database into 4 sheets, 1000 rows each:

It's simple. Let's say my data has 4 columns from A to D I make sheets named 1-1000, 1001-2000, etc. In each one I put a formula:

1)

=query(Master!A1:D,"select * limit 1000 offset 0")

=query(Master!A1:D,"select * limit 1000 offset 1000")
  1. =query(Master!A1:D,"select * limit 1000 offset 2000")
=query(Master!A1:D,"select * limit 1000 offset 3000")

Etc.

In order to limit number of occurences of each schools, I have to count these occurences and define what is the minimal page number on which this student can be displayed (for example - 17th student from certain school can be on 1st page, but 27th can be at least on 2nd page. 60th student can be on third or further.

When I determine minimal page number, I can sort my data accordingly and display sorted by minimal number:

enter image description here

In this situation my query on next pages have additional parameters:

=query(Master!A1:G,"select A,B,C,D order by G limit 1000 offset 0")

I use column G for sorting, but I don't display it.

You can find my solution here: https://docs.google.com/spreadsheets/d/1TP6MlMmLiUExOELFhgZnti7LR7VQouMg3h-X7QRcHzQ/copy

Names are generated randomly from polish names generator.

Upvotes: 1

Related Questions