VBA code to pick data based on multiple conditions

tirumal

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

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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

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

tirumal

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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
 

tirumal

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

Well-known Member
Joined
Mar 11, 2015
Messages
5,260
Office Version
365
Platform
Windows
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

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

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

Forum statistics

Threads
1,089,518
Messages
5,408,755
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top