I am starting with a spreadsheet of ~55,000 rows of data, which is formatted as: column 1 - Product ID, column 2 - Product Name, column 3 - # of Products.
I'm looking to split the 55k rows equally into 55 separate sheets, based on the # of products. So I cleaned up the original spreadsheet of 50K rows into this:
| Product Count | Count |
|---|---|
| 1 | 33693 |
| 2 | 9243 |
| 3 | 3955 |
| 4 | 2251 |
| and so on |
So, each 55 individual sheets should have ~1000 products, and within those 1000 products, products that have a product count of x should be equally distributed. So, in each of the 55 sheets, there should be 612 rows of data where product count is 1 (33693/55), 168 where product count is 2 (9243/55), 71 where product count is 3 (3955/55) and so on and so forth.
I have it set up where I have the original sheet, this new cleaned up sheet where I'm counting rows based on the product count value, and 55 blank sheets. Is there a non-manual way of being able to do this in Google Sheets? Is there a macro that I can use to help make this a less painful process? If I need to clarify anything, happy to do so. Thank you!
CodePudding user response：
try:
=QUERY(Sheet1!A:C; "limit 1000"; 1)
=QUERY(Sheet1!A:C; "limit 1000 offset 1000"; 1)
=QUERY(Sheet1!A:C; "limit 1000 offset 2000"; 1)
=QUERY(Sheet1!A:C; "limit 1000 offset 3000"; 1)
...