Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on formula.
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on formula.
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 --- "Scott" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on formula.
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... -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Max" wrote: 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 --- "Scott" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on formula.
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 --- "JBeaucaire" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|