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]

I suggest you download XL2BB and post some sample data
Some members will not click on a link
Also use XL2BB to post expected results so that anyone trying to help san see what you are trying to achieve

thanks

 

[Yongle]

Using your linked file
Try putting this formula in cell E2 and copy down
=IF(COUNTIFS(B$2:B2,B2,C$2:C2,C2)<=5,"Y","")

Does that allow you to filter the correct Tasks (using data filter) ?

Why do you need VBA ?
- what should happen to the filtered items

 

[tirumal]

Thanks for the reply!
The formula doesn't help, because not both the tasks from each Job ID is picked. Example:

test (2).xlsx
	A	B	C	D	E
26	Job 20	Assignee 2	Task1	Assignee 2Task1	Y
27	Job 20	Assignee 4	Task2	Assignee 4Task2	Y
28	Job 21	Assignee 1	Task1	Assignee 1Task1	Y
29	Job 21	Assignee 3	Task2	Assignee 3Task2	Y
30	Job 22	Assignee 2	Task1	Assignee 2Task1	Y
33	Job 23	Assignee 4	Task1	Assignee 4Task1	Y
34	Job 24	Assignee 2	Task2	Assignee 2Task2	Y
35	Job 24	Assignee 4	Task1	Assignee 4Task1	Y
39	Job 26	Assignee 3	Task2	Assignee 3Task2	Y
40	Job 27	Assignee 3	Task1	Assignee 3Task1	Y
43	Job 28	Assignee 3	Task1	Assignee 3Task1	Y
45	Job 29	Assignee 2	Task2	Assignee 2Task2	Y
Sheet9

This actually does filter the data, task wise for each asignee. But both task 1 and task 2 from each job ID should be picked. The IDs that don't meet the criteria can be ignored. But Finally the result should be this: Each assignee should have 5 task1's and task2's each but both task1 and and task2 of any job ID SHOULD be picked

 

[tirumal]

This is how the result should be:

Data sample.xlsm
	G	H	I	J
10	Assignee	Task1	Task2	Grand Total
11	Assignee 1	5	5	10
12	Assignee 2	5	5	10
13	Assignee 3	5	5	10
14	Assignee 4	5	5	10
15	Grand Total	20	20	40
Sheet11

and at the same time, both the tasks from each Job ID should be picked:

Data sample.xlsm
	O	P	Q	R
1	IDs	Task1	Task2	Grand Total
2	Job 1	1	1	2
3	Job 10	1	1	2
4	Job 11	1	1	2
5	Job 12	1	1	2
6	Job 13	1	1	2
7	Job 14	1	1	2
8	Job 15	1	1	2
9	Job 16	1	1	2
10	Job 17	1	1	2
11	Job 18	1	1	2
12	Job 19	1	1	2
13	Job 2	1	1	2
14	Job 20	1	1	2
15	Job 21	1	1	2
16	Job 24	1	1	2
17	Job 3	1	1	2
18	Job 25	1	1	2
19	Job 26	1	1	2
20	Job 4	1	1	2
21	Job 5	1	1	2
22	Grand Total	20	20	40
Sheet11

Im not able to achieve the desired result through excel formulas as it is not satisfying all the conditions.

 

[Yongle]

I am not following you at all

You said - I want to filter data in such a way that the macro should pick only five task1's and task2's of each assignee.
My formula for assignee 1 would return the following results

Book1
	A	B	C	D	E	F
1	ID	Assignee	Task	Concatenate	Formmula	Assignee 1
2	Job 1	Assignee 1	Task2	Assignee 1Task2	Y	Assignee 1Task2
4	Job 10	Assignee 1	Task1	Assignee 1Task1	Y	Assignee 1Task1
5	Job 10	Assignee 1	Task2	Assignee 1Task2	Y	Assignee 1Task2
6	Job 11	Assignee 1	Task2	Assignee 1Task2	Y	Assignee 1Task2
10	Job 13	Assignee 1	Task1	Assignee 1Task1	Y	Assignee 1Task1
16	Job 16	Assignee 1	Task2	Assignee 1Task2	Y	Assignee 1Task2
18	Job 17	Assignee 1	Task1	Assignee 1Task1	Y	Assignee 1Task1
22	Job 19	Assignee 1	Task1	Assignee 1Task1	Y	Assignee 1Task1
23	Job 19	Assignee 1	Task2	Assignee 1Task2	Y	Assignee 1Task2
28	Job 21	Assignee 1	Task1	Assignee 1Task1	Y	Assignee 1Task1
Sheet9

and for the assignee 2

Book1
	A	B	C	D	E	F
1	ID	Assignee	Task	Concatenate	Formmula	Assignee 1
11	Job 13	Assignee 2	Task2	Assignee 2Task2	Y	Assignee 2Task2
12	Job 14	Assignee 2	Task1	Assignee 2Task1	Y	Assignee 2Task1
14	Job 15	Assignee 2	Task1	Assignee 2Task1	Y	Assignee 2Task1
17	Job 16	Assignee 2	Task1	Assignee 2Task1	Y	Assignee 2Task1
26	Job 20	Assignee 2	Task1	Assignee 2Task1	Y	Assignee 2Task1
30	Job 22	Assignee 2	Task1	Assignee 2Task1	Y	Assignee 2Task1
34	Job 24	Assignee 2	Task2	Assignee 2Task2	Y	Assignee 2Task2
45	Job 29	Assignee 2	Task2	Assignee 2Task2	Y	Assignee 2Task2
46	Job 3	Assignee 2	Task2	Assignee 2Task2	Y	Assignee 2Task2
49	Job 30	Assignee 2	Task2	Assignee 2Task2	Y	Assignee 2Task2
Sheet9

 

[tirumal]

Correct! That's the first condition. But the second condition is: Whenever a Job ID is picked during the process, both task1 and task2 of the job ID should be picked since each job ID has both tasks.

I've tried using these excel formulas:

-I've concatenated the Assignee and tasks and check for repetition of the same throughout that column using =COUNTIFS($D$2:D2,D2)

-Now i've checked if each Job ID satisfies the given condition - =IF(AND(E3<=5,E2<=5),"Yes","No")

-This only resulted in filtering data that satisfies the condition but not enough data due to formulas' inability to automate the process of multiple conditions.

-Please find the link to the file here In all respective sheets, i've put the desired result and result achieved.

 

[Yongle]

I am now away from my PC until Tuesday, so cannot test or download anything

Please test this

SORT the data by JOB and the formula will give you a different result
- does that give you what you want?

 

[tirumal]

I did the same with the formulas mentioned above. Thanks for the suggestion though. What im thinking is to automatically delete each row with macro until the desired result is achieved. As rows gets delted, the probablity is shifted to next job. Can macro delete rows automatically given the conditions to be met or satisy the conditions in a pivot table? like 5 task1's and 2's to each asignee given both task's in job IDs' are covered

 

[tirumal]

I am now away from my PC until Tuesday, so cannot test or download anything

Please test this

SORT the data by JOB and the formula will give you a different result
- does that give you what you want?

You can find the macro free workbook here - File link