Macro/VBA for dividing a tabble

[nicolas877]

Hi forum , i have been looking for some vba code for doing this but i found no one yet. Basically it will be to do something like this ...lets say you have this table with the name colum in blank

CASE	NAME
2345​
4535​
5654​
2422​

To transform it to this , to assign half of the cases to mike , and half to susan (half of the table to one , and half to the other) , any sugestion ? thanks

CASE	NAME
2345​	Mike
4535​	Mike
5654​	Susan
2422​	Susan

 

[SpillerBD]

How about simple.
Why not put "Mike" for the first case, then "Susan" for the second case, then do the Fast-Fill Down (Angry Rabbit)?

 

[nicolas877]

How about simple.
Why not put "Mike" for the first case, then "Susan" for the second case, then do the Fast-Fill Down (Angry Rabbit)?

the real table has hundreds of rows but i need to dived at the middle and assign half of the casses to susan and half to mike

 

[SpillerBD]

Okay, a formula would be.
=IF(COUNT(A2:$A$403)/(COUNT(A:A))<=0.5,"Susan","Mike")
Then a Copy, Paste Values.

Could your list of Names grow?
Is your dataset a range or Table?
I know a lot of others on the forum can VBA this a lot faster than myself.

 

[SpillerBD]

In VBA, Note this assumes the NAME is not filled in and would overwrite any values.
Adjust the destination columns referenced.

Sub CaseDivision()
Dim LastRow As Long
Dim MidRow As Long

Dim SS As Worksheet
Set SS = ActiveSheet
'Application.ScreenUpdating = False

LastRow = SS.UsedRange.Rows.Count

SS.Activate

LastRow = SS.UsedRange.Rows.Count
MidRow = Round(((LastRow - 1)) / 2, 0)


SS.Range("B2:B" & MidRow + 1).Value = "Mike"
SS.Range("B" & MidRow + 2 & ":B" & LastRow).Value = "Susan"
SS.Range("A1").Select

End Sub

 

[nicolas877]

thank you spiller , the vba code worked perfectly