Equal list values.
in [Programming]
|
Prev: 2 processors
Next: Vlookup from closed excel file
From: Helmut Meukel on 1 Jun 2010 06:06 Darren, reading your OP and replies, I assume the number of team members should be equal too. Yes? If you have an even total number of team members it's easy: Sort the list, then put the highest and the lowest into group A and the second highest and the second lowest into group B Repeat this until the not jet assigned is <4. If there are 2 unassigned, sum up both groups and assign accordingly. Helmut. "Darren" <Darren(a)discussions.microsoft.com> schrieb im Newsbeitrag news:86F00890-B44F-44C5-BD88-9B1525182033(a)microsoft.com... > Sorry for so many replies. What I can't do is generate the lists manually. > Straight away I'd be accused of 'team fixing'. I need the teams to be as > equal as possible but randomly generated. That way, if there's a query, I can > show that I had no input in the lists. > > "Darren" wrote: > >> Doing it as I presume you mean I have 2 totals. The values are 52,898 and >> 52,488. As you can see there is a considerable difference between the 2. I >> want them to be as close as possible to eachother. >> >> "Darren" wrote: >> >> > Thanks for the reply Jacob. The problem I have is that the actual list is >> > over 200 names. The sort bit I can do (highest to lowest). When you say >> > assign subsequent rows, do you mean manually drag every other name and >> > number >> > to 2 new column sets? I was really hoping for something that would do this >> > for me. The list of names isn't constant. it changes from month to month, >> > as >> > do the values of column B >> > >> > "Jacob Skaria" wrote: >> > >> > > Sort the list by ColB and assign subsequent rows to each set. >> > > >> > > -- >> > > Jacob (MVP - Excel) >> > > >> > > >> > > "Darren" wrote: >> > > >> > > > I have a list of names in column A with numerical values in column B >> > > > >> > > > Example: >> > > > A B >> > > > Alan 1234 >> > > > Darren 2433 >> > > > Tammi 2055 >> > > > Carol 1999 >> > > > Rose 2001 >> > > > Reese 2411 >> > > > >> > > > Is it possible to equally split the list into 2 columns so that the >> > > > number >> > > > totals (B) are the same (or as near as)?
From: sali on 1 Jun 2010 08:00 "Darren" <Darren(a)discussions.microsoft.com> je napisao u poruci interesnoj grupi:CBFAF90D-0E3B-43B4-82D2-6F49D2506584(a)microsoft.com... > Is it possible to equally split the list into 2 columns so that the number > totals (B) are the same (or as near as)? here is a small sub that does the job just have your column [just values] selected, and call the sub 'divlist0' it inserts the new ws with splitted coluimns be carefull [or patient] execution time is exponential on elements number!! i have tested on 20 elements, 1 second on my cpu have a fun! =============================== 'equal split list by value/total Option Explicit Option Base 0 'new ws added Sub divlist0() Dim r As Range, c As Range, ulaz As Variant, i As Integer, izlaz As Variant Dim aws As Worksheet, ws As Worksheet Set r = Selection ulaz = Array() ReDim ulaz(r.Cells.Count) i = 0 For Each c In r.Cells ulaz(i) = c.Value i = i + 1 Next divlist1 ulaz, izlaz Set ws = Worksheets.Add For i = 0 To UBound(izlaz) ws.Cells(i + 1, izlaz(i)).Value = ulaz(i) Next End Sub Sub divlist1(ulaz As Variant, ByRef izlaz As Variant) Dim komada As Integer, komada1 As Integer, i As Integer Dim s1 As Double, stest As Double, dif1 As Double Dim bit(1000) As Boolean, bit2(1000) As Boolean komada = UBound(ulaz) komada1 = komada + 1 stest = 0 For i = 0 To komada stest = stest + ulaz(i) Next stest = stest / 2 dif1 = stest 'bit(i)=false Do While Not bit(komada + 1) 'overflow->end For i = 0 To komada1 'increase w/overflow bit(i) = Not bit(i) If bit(i) Then Exit For End If Next s1 = 0 For i = 0 To komada 'sum If bit(i) Then s1 = s1 + ulaz(i) End If Next If Abs(s1 - stest) < dif1 Then 'test best dif1 = Abs(s1 - stest) For i = 0 To komada bit2(i) = bit(i) Next End If Loop izlaz = Array() ReDim izlaz(komada) For i = 0 To komada If bit2(i) Then izlaz(i) = 1 Else izlaz(i) = 2 End If Next End Sub ===================================
From: Helmut Meukel on 1 Jun 2010 08:15 After more thinking a came up with annother way to do it: Sort the list as Jacob suggested, then assign the members to the two groups (say group A and group B) first to A second to B third to B forth to A fifth to A sixth to B .... and so on. To write code to do this is easy. Depending on the actual values, the approach I first suggested or this may get you the better distribution. So how about programming and running both and select the actually better? The results would by no means be the "best", but you could add more optimizing by swapping member between the groups to minimize the remaining difference. Of course these additional otimizations or other approaches may minimize the difference between the group sums better, however increase the difference in the number of group members or the distribution of members with high, medium, and low 'scores' between both groups. Helmut. "Helmut Meukel" <NoSpam(a)NoProvider.de> schrieb im Newsbeitrag news:uJ2lQIXALHA.4308(a)TK2MSFTNGP04.phx.gbl... > Darren, > > reading your OP and replies, I assume the number of team members > should be equal too. Yes? > > If you have an even total number of team members it's easy: > Sort the list, then put the highest and the lowest into group A > and the second highest and the second lowest into group B > Repeat this until the not jet assigned is <4. If there are 2 unassigned, > sum up both groups and assign accordingly. > > Helmut. > > > "Darren" <Darren(a)discussions.microsoft.com> schrieb im Newsbeitrag > news:86F00890-B44F-44C5-BD88-9B1525182033(a)microsoft.com... >> Sorry for so many replies. What I can't do is generate the lists manually. >> Straight away I'd be accused of 'team fixing'. I need the teams to be as >> equal as possible but randomly generated. That way, if there's a query, I can >> show that I had no input in the lists. >> >> "Darren" wrote: >> >>> Doing it as I presume you mean I have 2 totals. The values are 52,898 and >>> 52,488. As you can see there is a considerable difference between the 2. I >>> want them to be as close as possible to eachother. >>> >>> "Darren" wrote: >>> >>> > Thanks for the reply Jacob. The problem I have is that the actual list is >>> > over 200 names. The sort bit I can do (highest to lowest). When you say >>> > assign subsequent rows, do you mean manually drag every other name and >>> > number >>> > to 2 new column sets? I was really hoping for something that would do this >>> > for me. The list of names isn't constant. it changes from month to month, >>> > as >>> > do the values of column B >>> > >>> > "Jacob Skaria" wrote: >>> > >>> > > Sort the list by ColB and assign subsequent rows to each set. >>> > > >>> > > -- >>> > > Jacob (MVP - Excel) >>> > > >>> > > >>> > > "Darren" wrote: >>> > > >>> > > > I have a list of names in column A with numerical values in column B >>> > > > >>> > > > Example: >>> > > > A B >>> > > > Alan 1234 >>> > > > Darren 2433 >>> > > > Tammi 2055 >>> > > > Carol 1999 >>> > > > Rose 2001 >>> > > > Reese 2411 >>> > > > >>> > > > Is it possible to equally split the list into 2 columns so that the >>> > > > number >>> > > > totals (B) are the same (or as near as)? > >
From: Darren on 1 Jun 2010 08:18 Hi Helmut, yes your assumption is right. I want 2 teams of equal numbers with appoximately the same totals. Doing it highs and lows as you suggested gives more or less the same result as the suggestion by Jacob. The only change being a few names and a greater difference between the 2 totals. But I still have to do it manually. I was looking for a way to somehow sum up the total of colB divide it by 2 then sort through the values in colB so that they equal (or come close to) half of the colB total referencing the name also. Is there a code that could be written to do this type of calculation? "Helmut Meukel" wrote: > Darren, > > reading your OP and replies, I assume the number of team members > should be equal too. Yes? > > If you have an even total number of team members it's easy: > Sort the list, then put the highest and the lowest into group A > and the second highest and the second lowest into group B > Repeat this until the not jet assigned is <4. If there are 2 unassigned, > sum up both groups and assign accordingly. > > Helmut. > > > "Darren" <Darren(a)discussions.microsoft.com> schrieb im Newsbeitrag > news:86F00890-B44F-44C5-BD88-9B1525182033(a)microsoft.com... > > Sorry for so many replies. What I can't do is generate the lists manually. > > Straight away I'd be accused of 'team fixing'. I need the teams to be as > > equal as possible but randomly generated. That way, if there's a query, I can > > show that I had no input in the lists. > > > > "Darren" wrote: > > > >> Doing it as I presume you mean I have 2 totals. The values are 52,898 and > >> 52,488. As you can see there is a considerable difference between the 2. I > >> want them to be as close as possible to eachother. > >> > >> "Darren" wrote: > >> > >> > Thanks for the reply Jacob. The problem I have is that the actual list is > >> > over 200 names. The sort bit I can do (highest to lowest). When you say > >> > assign subsequent rows, do you mean manually drag every other name and > >> > number > >> > to 2 new column sets? I was really hoping for something that would do this > >> > for me. The list of names isn't constant. it changes from month to month, > >> > as > >> > do the values of column B > >> > > >> > "Jacob Skaria" wrote: > >> > > >> > > Sort the list by ColB and assign subsequent rows to each set. > >> > > > >> > > -- > >> > > Jacob (MVP - Excel) > >> > > > >> > > > >> > > "Darren" wrote: > >> > > > >> > > > I have a list of names in column A with numerical values in column B > >> > > > > >> > > > Example: > >> > > > A B > >> > > > Alan 1234 > >> > > > Darren 2433 > >> > > > Tammi 2055 > >> > > > Carol 1999 > >> > > > Rose 2001 > >> > > > Reese 2411 > >> > > > > >> > > > Is it possible to equally split the list into 2 columns so that the > >> > > > number > >> > > > totals (B) are the same (or as near as)? > > > . >
From: sali on 1 Jun 2010 09:55
"Darren" <Darren(a)discussions.microsoft.com> je napisao u poruci interesnoj grupi:2A2ADB03-163A-4D5A-91B6-1742107B3714(a)microsoft.com... > Is there a code that could be written to do this type of calculation? ha, this makes the-absolutely-best splitting modified my previous, to force equal-length-sublist [if wanted] nothing manual! ======================== Option Explicit Option Base 0 Sub divlist0() Dim r As Range, c As Range, ulaz As Variant, i As Integer, izlaz As Variant Dim ws As Worksheet Set r = Selection ulaz = Array() ReDim ulaz(r.Cells.Count - 1) i = 0 For Each c In r.Cells If Not IsNumeric(c.Value) Then MsgBox "not-a-number" Exit Sub End If ulaz(i) = CDbl(c.Value) i = i + 1 Next If i Mod 2 <> 0 Then MsgBox "odd" Exit Sub End If divlist1 ulaz, izlaz, True 'symetric divide, or any-type Set ws = Worksheets.Add For i = 0 To UBound(izlaz) ws.Cells(i + 1, izlaz(i)).Value = ulaz(i) Next End Sub Sub divlist1(ulaz As Variant, ByRef izlaz As Variant, symetric As Boolean) Dim komada As Integer, komada1 As Integer, i As Integer, komada2 As Integer, k As Integer, ok As Boolean Dim s1 As Double, stest As Double, dif1 As Double Dim bit(1000) As Boolean, bit2(1000) As Boolean komada = UBound(ulaz) komada1 = komada + 1 komada2 = komada1 / 2 stest = 0 For i = 0 To komada stest = stest + ulaz(i) Next stest = stest / 2 dif1 = stest 'bit(i)=false Do While Not bit(komada1) 'overflow->end For i = 0 To komada1 'increase w/overflow bit(i) = Not bit(i) If bit(i) Then Exit For End If Next s1 = 0 k = 0 For i = 0 To komada 'sum If bit(i) Then s1 = s1 + ulaz(i) k = k + 1 End If Next ok = True If symetric And k <> komada2 Then ok = False End If If Abs(s1 - stest) < dif1 And ok Then 'test best dif1 = Abs(s1 - stest) For i = 0 To komada bit2(i) = bit(i) Next End If Loop izlaz = Array() ReDim izlaz(komada) For i = 0 To komada If bit2(i) Then izlaz(i) = 1 Else izlaz(i) = 2 End If Next End Sub ======================== |