VBA code to pick data based on multiple conditions

[tirumal]

Hello guys,



I've been struggling to write this for days macro and I have finally decided to ask for your help.


Here's what I have:



Sheet1 - Job IDs, Assignees and Tasks as headers



A1 - IDs

B1 - Assignees

C1 - Tasks

*Rest of the columns are something i've tried which did not work out.



There are different no. of Jobs in the data and each job has 2 tasks only. The tasks (task1 and task2) of each job are done by either 2 assignees separately or done by the same assignee (for some jobs).



What I am trying to achieve:



- I want to filter data in such a way that the macro should pick only five task1's and task2's of each assignee.

- But ideally, when a task2 of a job is picked, task 1 of the same job should also be picked keeping in mind that the assignee that did that task did not have more than 5 task1;s (or) task2's picked.

- My idea is, the macro should be able to ideally keep a log or delete data no. of tasks of a particular assignee once they reach the threshold of 5.

- To put it in simple terms, the no. of jobs picked do not matter, but both task 1 and 2 of any Job should be picked and each assignee should equally have 5 task1 and task2's.



I had multiple attempts but I have failed miserably. I would be really grateful if someone helps me out.

*Please find the file link here

Thank you,
Tirumal

 

[Yongle]

Debug.Print writes to the Immediate Window
It is a useful tool when trying to see what is going on with variables and values etc, especially when looping through many cells
The Immediate Window is displayed (when in VBA editor) using shortcut {CTRL} g or by clicking on menu tab View and then Immediate Window

To return items being rejected due to either count of items exceeding 5
... remove the apostrophe before these 2 lines in the latest version of the code

                        'Debug.Print "failed CountIf",r, ID, Mgr1, Opt1
                        'Debug.Print "failed CountIf",r, ID2, Mgr2, Opt2

 

[tirumal]

Hi, Thanks for getting back
Your code is doing wonders as per allocation.
I've changed the number from maximum number 5 to 49 and ran the code on whole data.
The result is coming out as desired for most of the cases. But i'm not sure why this is happening:

Please check the yellow and red marks (E and F columns contain entire original True and false data as per manager):

Allocation test.xlsm
	A	B	C	D	E	F
30	mtshah	49	26	75	512	440
31	nikumark	49	49	98	78	512
32	panchada	49	49	98	122	400
33	pnrhul	49	49	98	175	170
34	rdnsh	15	23	38	15	23
35	rogelios	6	12	18	6	12
36	saithom	49	47	96	368	261
37	sanikh	49	49	98	454	389
38	sattili	49	49	98	184	233
39	shprakha	49	49	98	110	324
40	siaditi	49	49	98	87	413
41	skaveesh	49	39	88	430	445
42	smantral	49	49	98	136	147
43	snghoj	49	49	98	986	372
44	sshaka	49	49	98	395	278
45	tulap	49	49	98	175	217
46	tvinaya	1	11	12	1	11
47	vnchan	49	49	98	228	157
48	wajakhan	49	49	98	256	355
49	Grand Total	1928	1934	3862	11905	11905
Sheet4

- The true and false are not equally distributed in the end and people marked in yellow have a huge variance b/w true and false where false is much lower. False data cannot be lower than true data when there's enough samples in the original data right?
- Is this because of any data limitation ?
or the value 49 i've put ?

 

[Yongle]

The code is working correctly - but is working through JOB by JOB
By cancelling it after 50 you get the first found 50 jobs
In the meantime run the sub without your 50 restriction
I have an idea - will update thread shortly

 

[Yongle]

Sorry - I just realised I misunderstood what you said last time - ignore previous reply

 

[tirumal]

Reason for putting 50 is because i need the data to be allocated for same (50-60) or more sometimes.
The code is running perfectly but
The true and false allocation itself is not equal. Also False data (Column C) is lower for some people.

Allocation test.xlsm
	A	B	C	D
39	saithom	50	48	98
40	ayumishr	50	41	91
41	skaveesh	50	40	90
42	biswadm	50	35	85
43	elisaty	32	34	66
44	mtshah	50	27	77
45	rdnsh	15	23	38
46	rogelios	6	12	18
47	tvinaya	1	11	12
48	minhaza		1	1
49	Grand Total	1966	1972	3938
Sheet6

I'm guessing if it's data limitation ?
Will putting extra data help?

 

[Yongle]

I am currently testing something that may help you

 

[Yongle]

I have found an approach that may work and will update the thread within the next 2 hours after I have fully tested revised code

 

[tirumal]

I have found an approach that may work and will update the thread within the next 2 hours after I have fully tested revised code

Sure! Will be looking forward to the same.

 

[Yongle]

The code ran much too slowly when tested
I will revert when I have resolved that issue

 

[tirumal]

The code ran much too slowly when tested
I will revert when I have resolved that issue

Sure!
It actually took 7:45 Mins for me btw