allocation to user

[manojxls]

hi,
can you help me to formula or vba to allocate rows to three user equally.

like in column "a" i have some data want to allocate to three users.
suppose if column a having 100 rows each user will get 33, 33 and remain 34.

barcode1	user1
barcode2	user2
barcode3	user3
barcode6	user1
barcode5	user2
barcode8	user3
barcode9

 

[Legacy 143009]

Assuming you have list of users in column D, column B formula will be:

=INDEX($D:$D,MOD(ROW()-1,COUNTA($D:$D))+1)

This will always distribute users equally no matter how many you have.

 

[Peter_SSs]

This will always distribute users equally no matter how many you have.

But it will give very strange results if subsequently any new rows are inserted at the top of the worksheet (eg for headings)
This would be more robust.

24 01 09.xlsm
	A	B	C	D
1	Barcode	User		User List
2	barcode1	user1		user1
3	barcode2	user2		user2
4	barcode3	user3		user3
5	barcode6	user1
6	barcode5	user2
7	barcode8	user3
8	barcode9	user1
Distribute

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(D$2:D$4,MOD(ROWS(B$2:B2)-1,3)+1)

If the number of users might change, then this would still distribute as evenly as possible.

24 01 09.xlsm
	A	B	C	D
1	Barcode	User		User List
2	barcode1	user1		user1
3	barcode2	user2		user2
4	barcode3	user3		user3
5	barcode6	user4		user4
6	barcode5	user1
7	barcode8	user2
8	barcode9	user3
Distribute (2)

Cell Formulas
Range	Formula
B2:B8	B2	=INDEX(D$2:D$100,MOD(ROWS(B$2:B2)-1,COUNTA(D$2:D$100))+1)

 

[manojxls]

Thanks All.