My Excel Sheet 1 has the following Data
Sl.No(Column1) ,Employee Name(Column2) , Group Code(Column3) , Employee Group Number(Column4) .
Sl.No and Employee Name has the data.
In another Entry Sheet 3 data has to feed.
1. Total Group:
2. Group Start Number:
3. Members per Group:
[/B]
This is my Excel book structure.
___________________
Each Group has One Leader, One Assistant and 7-10 members. (E.g: Group :1+1+7)
if we feed in Entry Sheet we enter Total Groups = 10, Group Start Number = 011 , Members per Group=7
then Group Code column will be filled by Group1,Group2, ....Group 10 (Upto Total Groups)
and the same in Employee Group Number column will be assigned as
First 10 (Total teams) employees as Leader (Code will Prefix with LRwith Group Code 3 digit.) LR011,LR012,LR013...
Next 10 employees as Assistant(Code will Prefix with ATwith 3digit Group Code.) AT011,AT012,AT013...
(Both Leader and Assistant number will be same except the prefix)
From Next 10 by 10 as members to the each group.
As
For employee prefix is EMP ,Next two digit denotes Group Code and Last digits denotes Employee Number in that Group.
EMP111
EMP121
EMP131
EMP141...
For 5 Group Demo:
Group Code Employee Group Number
Group 1 LR011
Group 2 LR012
Group 3 LR013
Group 4 LR014
Group 5 LR015
Group 1 AT011
Group 2 AT012
Group 3 AT013
Group 4 AT014
Group 5 AT015
Group 1 EMP111
Group 2 EMP121
Group 3 EMP131
Group 4 EMP141
Group 5 EMP151
Group 1 EMP112
Group 2 EMP122
Group 3 EMP132
Group 4 EMP142
Group 5 EMP152
Group 1 EMP113
Group 2 EMP123
is it possible to do without VBA (with helper columns/sheet)?
Share your ideas with VBA/without VBA..
Bookmarks