VBA to divide and assign rows

[nicolas877]

Hi forum, i came across with this problem. I need to divide certain rows by an input number to assign them to people. Like This

CASE - NAME
777
333
999
993
101
190

to this...(in this will be dividing by 3, to assign 3 people)

CASE - NAME
777 Juan
333 Juan
999 Kate
993 Kate
101 Leon
190 Leon

But the quantity of names can be variable , line 2 names ,3 names, 4 names...so there is a way to divide all the rows by certain input in a msg box an then assign a name to each division ? I only know static division by 2 in the middle with MidRow command. Thank you in advance

 

[lrobbo314]

How about this?

2022 OTM Arrivals Mexicali Airport Weekly (version 1).xlsb
	A	B	C	D
1	777	Juan		Juan
2	333	Juan		Kate
3	999	Kate		Leon
4	993	Kate
5	101	Leon
6	190	Leon
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=INDEX($D$1:$D$3,((ROW()-1)/(COUNTA($A$1:$A$6)/COUNTA($D$1:$D$3)))+1)

 

[Joe4]

How about this?

2022 OTM Arrivals Mexicali Airport Weekly (version 1).xlsb
	A	B	C	D
1	777	Juan		Juan
2	333	Juan		Kate
3	999	Kate		Leon
4	993	Kate
5	101	Leon
6	190	Leon
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=INDEX($D$1:$D$3,((ROW()-1)/(COUNTA($A$1:$A$6)/COUNTA($D$1:$D$3)))+1)

There is a potential problem with that.
If the number of records divided by the number of people is not perfectly divisible (with no remainder), the last few records will return errors.
And I imagine that it quite commomly will not divide perfectly.

 

[nicolas877]

Thank you Irobbo314 for the formula but as Joe4 says it doesnt work as is not possible to divide when there is a remainder

 

[Joe4]

We can probably use VBA to do that. Can you explain what should happen when there is an odd number like that?
In this case, would you want 3 to the first person, 3 to the second, and 2 to the third?

 

[nicolas877]

Yes that would be ok

 

[Joe4]

OK, a few more questions.

What do you mean by this part?

.so there is a way to divide all the rows by certain input in a msg box

Do you mean that you want the VBA code to prompt them to enter the names in an input box?
If so, do you want them to enter all the names at once (separated by commas) within a single inputbox, or do you want them to repeatedly ask them for names, one after the other (so enter each name in a separate input)?

And, is it all right to do them by alternating through them, like this?

 

[nicolas877]

Like this
First an input box to enter the number all the rows will be divided , lets say there are 35 rows with data and we want to divide by 3, in a input box i enter a 3 , then another input box ask for name A , then another for name B, then another for name C.......the solution then will be the first 12 rows with name A , the next 12 rows with name B , the last 11 rows with name C

 

[Joe4]

So, to assign it in the manner like I showed in my previous post would not be acceptable then?
I am pretty sure I can come up with code to do it like that.
To do it the other way is a bit more challenging.

 

[bebo021999]

Assum case is from A1, name from B1
This code will ask for how many names input, then name 1, then name 2,...
Then paste the name list into column C, then assign each name in column B

Option Explicit
Sub divide()
Dim Lr&, Nr&, i&, k&, arr()
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Nr = InputBox("How many people?")
ReDim arr(1 To Lr, 1 To 1)
Range("C:C").ClearContents
    For i = 1 To Nr
        Range("C" & i).Value = InputBox(" person " & i & " name:")
    Next
        For i = 1 To Lr
            k = Int((i - 1) / (Int(Lr / Nr) + 1)) + 1
            arr(i, 1) = Range("C" & k).Value
        Next
Range("B1").Resize(Lr, 1).Value = arr
End Sub