Thanks for your answer, let me give you more details to help you understand my situation.
My table looks like this, and i have approx. 8000 records (use text to column separator " "):
ID UserID Category Value1 Value2 Group
351520 1801129226 B1 5,140.33 115.61 Group1
1430584 2850323070 B1 6,841.17 221.52 Group2
1412316 1690308070 B1 6,817.92 223.49 Group3
1253380 1780911434 B1 1803.89 109.44 Group1
1337470 2460820090 B2 3,675.36 248.64 Group1
1552594 2591019080 B2 3746.05 269.58 Group3
999728 2850323070 B2 3,700.43 255.50 Group2
586489 2590115511 B2 1,978.62 21.60 Group3
1362556 1549317360 B3 2,197.88 159.87 Group1
1147636 2690202900 B3 2,044.22 204.04 Group2
812250 7759108479 B3 2,163.30 127.90 Group3
1645765 7759108479 B3 1,843.00 190.15 Group1
1419227 2799813159 B3 2,003.27 115.93 Group2
1134840 1469917131 B3 2,011.01 193.99 Group3
I need to split the data into 3 groups (see column Group), the groups must have a equal number of records and a equal cumulated "value 1" and "value 2".
The data must be split taking in consideration the category, the rules mentioned above apply to each category.
Each record belongs to a user ("UserID"), if one record from a user goes to a group, the others must go to the same group, without changing the cumulated "Value 1" and "Value 2"
And in the future I might need to change the percentage for each group (40, 30, 30 or other combinations).
I currently use a set of queries to select randomly the first 40% values for each category and a union to put them together, after this i use a query to select the rest of 60% and split the records in 50% - 50%, see the code below:
SELECT DISTINCT Table.*
FROM
(SELECT TOP 40 PERCENT Table.UserID, Table.ID, Table.Category
FROM Table
ORDER BY rnd(INT(NOW*ID)-NOW*ID)) AS RandomRecords
LEFT JOIN Table ON RandomRecords.UserID = Table.UserID
WHERE ((Table.Category)="B2")
UNION ALL
SELECT DISTINCT Table.*
FROM
(SELECT TOP 40 PERCENT Table.UserID, Table.ID, Table.Category
FROM Table
ORDER BY rnd(INT(NOW*ID)-NOW*ID)) AS RandomRecords
LEFT JOIN Table ON RandomRecords.UserID = Table.UserID
WHERE ((Table.Category)="B3")
UNION ALL
. B4
. B5
...
. B12
Unfortunatelly there are categories with few records, so the cumulated "value 1" and "value 2" is not equal (not even close) between groups.
I'am open to sql or vba solutions.
Any help will be much appreciated.
Thx