VBA code to pick data based on multiple conditions

tirumal

Board Regular
Joined
Feb 16, 2020
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
Thanks for the reply!
The formula doesn't help, because not both the tasks from each Job ID is picked. Example:

test (2).xlsx
ABCDE
26Job 20Assignee 2Task1Assignee 2Task1Y
27Job 20Assignee 4Task2Assignee 4Task2Y
28Job 21Assignee 1Task1Assignee 1Task1Y
29Job 21Assignee 3Task2Assignee 3Task2Y
30Job 22Assignee 2Task1Assignee 2Task1Y
33Job 23Assignee 4Task1Assignee 4Task1Y
34Job 24Assignee 2Task2Assignee 2Task2Y
35Job 24Assignee 4Task1Assignee 4Task1Y
39Job 26Assignee 3Task2Assignee 3Task2Y
40Job 27Assignee 3Task1Assignee 3Task1Y
43Job 28Assignee 3Task1Assignee 3Task1Y
45Job 29Assignee 2Task2Assignee 2Task2Y
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
 
Upvote 0
This is how the result should be:

Data sample.xlsm
GHIJ
10AssigneeTask1Task2Grand Total
11Assignee 15510
12Assignee 25510
13Assignee 35510
14Assignee 45510
15Grand Total202040
Sheet11


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

Data sample.xlsm
OPQR
1IDsTask1Task2Grand Total
2Job 1112
3Job 10112
4Job 11112
5Job 12112
6Job 13112
7Job 14112
8Job 15112
9Job 16112
10Job 17112
11Job 18112
12Job 19112
13Job 2112
14Job 20112
15Job 21112
16Job 24112
17Job 3112
18Job 25112
19Job 26112
20Job 4112
21Job 5112
22Grand Total202040
Sheet11


Im not able to achieve the desired result through excel formulas as it is not satisfying all the conditions.
 
Upvote 0
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
ABCDEF
1IDAssigneeTaskConcatenateFormmulaAssignee 1
2Job 1Assignee 1Task2Assignee 1Task2YAssignee 1Task2
4Job 10Assignee 1Task1Assignee 1Task1YAssignee 1Task1
5Job 10Assignee 1Task2Assignee 1Task2YAssignee 1Task2
6Job 11Assignee 1Task2Assignee 1Task2YAssignee 1Task2
10Job 13Assignee 1Task1Assignee 1Task1YAssignee 1Task1
16Job 16Assignee 1Task2Assignee 1Task2YAssignee 1Task2
18Job 17Assignee 1Task1Assignee 1Task1YAssignee 1Task1
22Job 19Assignee 1Task1Assignee 1Task1YAssignee 1Task1
23Job 19Assignee 1Task2Assignee 1Task2YAssignee 1Task2
28Job 21Assignee 1Task1Assignee 1Task1YAssignee 1Task1
Sheet9


and for the assignee 2
Book1
ABCDEF
1IDAssigneeTaskConcatenateFormmulaAssignee 1
11Job 13Assignee 2Task2Assignee 2Task2YAssignee 2Task2
12Job 14Assignee 2Task1Assignee 2Task1YAssignee 2Task1
14Job 15Assignee 2Task1Assignee 2Task1YAssignee 2Task1
17Job 16Assignee 2Task1Assignee 2Task1YAssignee 2Task1
26Job 20Assignee 2Task1Assignee 2Task1YAssignee 2Task1
30Job 22Assignee 2Task1Assignee 2Task1YAssignee 2Task1
34Job 24Assignee 2Task2Assignee 2Task2YAssignee 2Task2
45Job 29Assignee 2Task2Assignee 2Task2YAssignee 2Task2
46Job 3Assignee 2Task2Assignee 2Task2YAssignee 2Task2
49Job 30Assignee 2Task2Assignee 2Task2YAssignee 2Task2
Sheet9
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,714
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top