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)?

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)?

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)?

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)?

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)?

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)?

"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
===================================

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" schrieb im Newsbeitrag
...
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)?

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" 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)?


.

"Darren" je napisao u poruci interesnoj
...

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
========================

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
===================================


.

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

"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