Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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)? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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)? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
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" schrieb im Newsbeitrag ... 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)? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
"Darren" je napisao u poruci interesnoj
... 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 =================================== |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
Thankyou Sali. Patience is the key. I broke my list into groups of 20,
totalled thier values then run the sequence again on the new list. Then just spilt them back up and added the original names. At least now I can show I had minimal input in the lists generated. "sali" wrote: "Darren" je napisao u poruci interesnoj ... 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 =================================== . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
"Darren" je napisao u poruci interesnoj ... Thankyou Sali. Patience is the key. I broke my list into groups of 20, "sali" wrote: "Darren" je napisao u poruci interesnoj ... Is it possible to equally split the list into 2 columns so that the number totals (B) are the same (or as near as)? be carefull [or patient] execution time is exponential on elements number!! i have tested on 20 elements, 1 second on my cpu ok, it is cpu intensive, but depending on your cpu, maybe few minutes will be ok for whole list in single-run, better put your comp to work [and have coffee in the meantime], than you to work manualy aranging sublists! just to notice, i've posted two subs, the second one forceing equal length results lists, the first one is forced to have the best possible equal sub-totals |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Equal list values.
Hello Darren,
I am working on your problem but not there yet. This site might may be dead soon. If you find that you are interested in my algorithm then please drop me a line. Best Regards, Gabor Sebo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting values from a list to equal a given value | Excel Worksheet Functions | |||
Variable equal to values in a list | Excel Programming | |||
counting the last 3 values not equal to x in a list | Excel Worksheet Functions | |||
Macro to select a list of values greater than or equal to a value | Excel Programming | |||
list values equal in 2 worksheets? | Excel Worksheet Functions |