Dynamically assignment for time booking codes to resources

Negi1984

Board Regular
Joined
May 6, 2011
Messages
189
Hi All,

I am looking for your expert suggestion if we can dynamically assign network codes to multiple resources using VBA ?

for example :-

Table 1 contains data related to resources name and , how much hours they can book in multiple network code.

NameSignumProductive Hrs.
AEASDWE
34​
BERTYUGF
42.5​
CEASQWET
42.5​
DQWESDTR
0​
EERTYERD
42.5​
FQWETFSF
42.5​
GJHYTRT
38​
HJKUYTBN
42.5​


Table 2 contains below data which contains network codes and maximum hrs resources can book on that code.

ICRRBHours
12386755​
147.25​
17643021​
12​
17643021​
66.25​
10745210​
20​
10345606​
40​
10775345​
15​


Now I want to create a final output where these network code should map to multiple resources and also needs to taken care of table 1 productive columns for how much a hrs. a resource can book(Maximum 1 resource can book 42.5 and minimum 0 table 1 contains that details).

Also we have to at least assign 2 ICRRB codes needs to assign to each resource and maximum upto 4 to the each resources.

For better understanding I have uploaded a sample file in given link.

Also sample output pasted below:-

ICRRBABCDEFGHRemaining
12386755​
5​
19.5​
30​
30​
12.5​
30​
20.25​
0​
17643021​
4​
8​
0​
17643021​
10​
10​
12.5​
12.5​
20​
1​
0.25​
0​
10745210​
15​
5​
0​
10345606​
1.25​
7​
22​
9.75​
10775345​
8.75​
6.25​
Total Productive
34​
42.5​
42.5​
0​
42.5​
42.5​
38​
42.5​
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,968
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top