Split data into 3 segements of each volume and value

Hello there,

From the attached data example (the actual data is roughly 54000 rows) - i need to create 3 sets of data that have as far as is possible an equal volume of account numbers with an equal value. I'm not entirely sure how to even start looking at this so thank you for your help in advance.

if it helps the volume of accounts is the priority ove thier value if we cannot get an excact even split.

is this not possible?

Hi,
The question as originally posed appears to me to be a fairly difficult problem. I suspect that it's possible to provide a simple 'good 'nuf' solution, but some more information is needed. So, let me pose a few possible solutions along with my suspicions of how they are inadequate.

Possible solution 1: copy rows 1-18000 into NewSheet1, 18001 - 36000 into NewSheet2, 36001 - 54000 into NewSheet3.

Potential problem; certain 'chunks' of account have different typical values associated with them. For example, accounts starting with '01' might typically be a few hundred pounds(Euros?), while accounts starting with '9' might typically be a few million pounds.

Possible solution 2: Copy each 3rd row to a new sheet. In a blank column, enter the formula

Please Login or Register  to view this content.

and using the auto filter copy/paste the rows with each of three values into three new worksheets.

Potential Problem: While this helps remove the effects of clustered 'chunks', and the relative size of each total will approach parity, the absolute difference will increase as the number of rows increase.

Possible solution 3: Sort the sheet by the values (descinding). In a blank column, enter the formula

Please Login or Register  to view this content.

Then, using the autofilter, copy/paste the rows contating 0 or 5 into NewSheet1, 1 or 4 into NewSheet2, and 2 or 3 into NewSheet3. Same problems as solution 2, but the absolute and relative differences should be smaller.

Hopefully, one of these methods provides an adequate solution: I'm not sure if I'm up for solving the problem as originally stated

Hi Cjo,

Many Thanks for your help - your solution worked perfectly. 3 units with excactly the same volume of accounts and their value is within 0.001% of one another.

Just out of curiosity, which method did the trick?