Need help on formula.

[Scott]

I have a range of data in two columns with anywhere between 1 and 144 rows
of
data. Data in Column A is in text format and contains all unique data. Data
in Column B is numeric and may or MAY NOT all be unique. All unique text
data has a numeric value associated with it. With this data range, I want to
create evenly proportioned groups (groups contain 2, 3, or 4 text values,
which I want to be able to change depending on the range size.)

For instance.
COLUMN A____COLUMN B
Text1 has a value of 10
Text2 has a value of 20
Text3 has a value of 30
Text4 has a value of 40
Text5 has a value of 50
Text6 has a value of 60
Text7 has a value of 70
Text8 has a value of 80

What I want to do with those 8 values is to create 2 evenly matched groups
of 4.
So I want the output to be:
Group 1
Text10 = 10
Text30 = 30
Text60 = 60
Text80 = 80
Total Pts = 180 <-- notice the even, or at least close to even, total points
between groups.

Group 2
Text2 = 20
Text4 = 40
Text5 = 50
Text7 = 70
Total Pts = 180 <--


How would I go about this? I want to put the output in one single column
without breaks. Thanks!

 

[Max]

One approach would be to simulate it up along these lines

Source data is assumed in A1:B8, viz.:
Text1 10
Text2 20
Text3 30
Text4 40
Text5 50
Text6 60
Text7 70
Text8 80

In C1: =RAND()
In D1: =INDEX(A$1:A$8,RANK($C1,$C$1:$C$8))
Copy D1 to E1. Select C1:E1, copy down to E8. D1:E8 will return a random
scramble of the source data. Pressing F9 regenerates the random scramble.

Then place in F1: =ABS(SUM(E1:E4)-SUM(E5:E8))
to monitor the absolute difference between the 1st 4 items' sum and the 2nd
4 items sum within the randomized scramble in D1:E8. Going by your logic, the
objective is to minimize the absolute difference in F1 (ideal would be F1 = 0)

The Play: Just press F9 to regenerate until F1 returns zero (or near zero).
Stop. You'd then have one set of desired results of the 2 groups, ie 1st
group in D1:E4, 2nd group in D5:E8.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

One approach would be to simulate it up along these lines

Source data is assumed in A1:B8, viz.:
Text1 10
Text2 20
Text3 30
Text4 40
Text5 50
Text6 60
Text7 70
Text8 80

In C1: =RAND()
In D1: =INDEX(A$1:A$8,RANK($C1,$C$1:$C$8))
Copy D1 to E1. Select C1:E1, copy down to E8. D1:E8 will return a random
scramble of the source data. Pressing F9 regenerates the random scramble.

Then place in F1: =ABS(SUM(E1:E4)-SUM(E5:E8))
to monitor the absolute difference between the 1st 4 items' sum and the 2nd
4 items sum within the randomized scramble in D1:E8. Going by your logic, the
objective is to minimize the absolute difference in F1 (ideal would be F1 = 0)

The Play: Just press F9 to regenerate until F1 returns zero (or near zero).
Stop. You'd then have one set of desired results of the 2 groups, ie 1st
group in D1:E4, 2nd group in D5:E8.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[JBeaucaire]

Similar to above, but rather than manually having to press F9 over and over,
here's a layout and a macro to do it in one click:

Text Values A1:A8
Numbers B1:B8
Formula in C4 =SUM(B1:B4)
Formula in C8 =SUM(B5:B8)

Now, here's the macro, run it and it shuffle the data until a matching set
is created and then stop.

Sub SortMatch()
Application.ScreenUpdating = False
Columns("C:C").Insert Shift:=xlToRight
Range("C1:C8").FormulaR1C1 = "=RAND()"

Start:
Range("A1:C8").Sort Key1:=Range("C1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

If [D4] = [D8] Then
Columns("C:C").Delete Shift:=xlToLeft
MsgBox "Found one set"
Else
GoTo Start
End If

Application.ScreenUpdating = True
End Sub

Hope that sets you up...

 

[JBeaucaire]

Similar to above, but rather than manually having to press F9 over and over,
here's a layout and a macro to do it in one click:

Text Values A1:A8
Numbers B1:B8
Formula in C4 =SUM(B1:B4)
Formula in C8 =SUM(B5:B8)

Now, here's the macro, run it and it shuffle the data until a matching set
is created and then stop.

Sub SortMatch()
Application.ScreenUpdating = False
Columns("C:C").Insert Shift:=xlToRight
Range("C1:C8").FormulaR1C1 = "=RAND()"

Start:
Range("A1:C8").Sort Key1:=Range("C1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

If [D4] = [D8] Then
Columns("C:C").Delete Shift:=xlToLeft
MsgBox "Found one set"
Else
GoTo Start
End If

Application.ScreenUpdating = True
End Sub

Hope that sets you up...

 

[Max]

1. > If [D4] = [D8] Then
How could the line above be amended to handle the scenario where the
condition is approximate, eg: stop the randomization if the absolute value of
D4 is within 5% of D8's ?

2. How could your sub be modified to re-generate & "print" several sets of
possible result combinations (say 3 result sets) to the right of the source
data in A1:B8 (let's assume the source data is left intact)

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Max]

1. > If [D4] = [D8] Then
How could the line above be amended to handle the scenario where the
condition is approximate, eg: stop the randomization if the absolute value of
D4 is within 5% of D8's ?

2. How could your sub be modified to re-generate & "print" several sets of
possible result combinations (say 3 result sets) to the right of the source
data in A1:B8 (let's assume the source data is left intact)

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik