Dividing n number of rows equally into separate sheets?

[farmer-born-66]

Every couple of days I get an uncertain amount of data in a spreadsheet. I divide the number of rows equally into 4 separate sheets to be analyzed by separate staff members.

So, for instance, if I get a spreadsheet with 115 rows of data (like today), then each staff member gets ~29 rows each. Tomorrow I might get only 20 rows of data, and the day after that it might be 600 rows.

Could someone help me write a macro that would divide a spreadsheet’s rows equally into 4 sheets, regardless of how many rows there are?

Thank you in advance!

[keme]

• Do you receive a spreadsheet file (ods, xlsx), some other data format calc handles (like csv), or something else (like a link to a data source which Calc can connect to)?
• Do you want to split in chunks (so the first fourth goes to staff#1), round robin fashion (so staff#1 gets item#1/5/9/…), some other way, or is the order less important?
• If you get a spreadsheet file, are the data interdependent (so content/meaning is lost/corrupted when connection to original dataset is severed)?

[farmer-born-66]

hi keme, thank you for the simple solution. in any case:

• i get spreadsheet files - mostly xlsx but also csv.
• doesnt matter if the data gets separated into chunks or round robin style. i just need it to be more or less equally divided
• data is not interdependent

[keme]

One simple solution to disribute entries “round robin”, no macro required:

• Add a column of “staff#” entries.
• Insert the formula =MOD(ROW();4) as first entry.
• copy down (double click the “fill handle” on the bottom right corner of th selection rectangle)
• Filter table on the new column

[steve2018]

+1 for the simplicity of the solution, where I assume the identical file is sent to everybody. Downsides: the 4 recipients must be allowed to see what the others are getting; filtering is a feature I like, but IMHO to prevent problems the OP should paste the values and then sort before sending out the file; the 1st recipient will have the highest workload over time and will know it if the OP isn’t manually separating into 4 separate files. Adding the week number to the formula might help to some degree.