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]

JOB ID's
- your sample data is not your REAL data
- provide an true example of a REAL Job ID
- are all ID's of EQUAL length
- do all contain the same alpha characters at the beginning ?

 

[tirumal]

I've attached the file with my real data.
- The ID's are alpha numeric
-Column A contains Job IDs
-Column b has the asignees
- In column C, True and False sepecifies Task1 and Task2 respectively.

Please find the macro free workbook with real data here

 

[Yongle]

I am not prepared to download your file from that site
As requested in my first post plesae use XL2BB to post your sample data

 

[tirumal]

Posting the sample here

xl2bb.xlam
	A
1
Sheet1

 

[tirumal]

Ignore the previous one:
Posting only few rows of data here

Test.xlsx
	A	B	C
1	Job ID	Manager	Dislike Option
2	000064c8e46c47cb9979dcc0268f223d	mmmadhr	TRUE
3	000064c8e46c47cb9979dcc0268f223d	bjjosh	FALSE
4	0002cfccbfb2485cb46ca73a465f76f6	biswadm	TRUE
5	0002cfccbfb2485cb46ca73a465f76f6	rdnsh	FALSE
6	00110102441d4b478512d5e7691249dd	sattili	TRUE
7	00110102441d4b478512d5e7691249dd	sattili	FALSE
8	001bce89bdba412a9120e6d86346509e	mtshah	TRUE
9	001bce89bdba412a9120e6d86346509e	sshaka	FALSE
10	001df3620e524224b4cd43d6575f8a63	biswadm	TRUE
11	001df3620e524224b4cd43d6575f8a63	elisaty	FALSE
12	0025cfb502dc4ff1833ce4ee36855055	agangele	TRUE
13	0025cfb502dc4ff1833ce4ee36855055	wajakhan	FALSE
14	002646548b2e4428aa059dbacbc7dc92	biswadm	TRUE
15	002646548b2e4428aa059dbacbc7dc92	nikumark	FALSE
16	002701827ff64d5a99c03e8794b481d1	snghoj	TRUE
17	002701827ff64d5a99c03e8794b481d1	alfrdav	FALSE
18	0029b3cdad244e6e9489f6cdcb4ccdd7	snghoj	TRUE
19	0029b3cdad244e6e9489f6cdcb4ccdd7	snghoj	FALSE
20	0029c74095cb4c5f848beb7bf158f630	mtshah	TRUE
21	0029c74095cb4c5f848beb7bf158f630	skaveesh	FALSE
22	0042544166e14942b4526401ba60dfcf	rdnsh	TRUE
23	0042544166e14942b4526401ba60dfcf	siaditi	FALSE
24	00434abee4e84c2f9785d6da6e149211	nkakulla	TRUE
25	00434abee4e84c2f9785d6da6e149211	mmmadhr	FALSE
26	00494aee7a0a40e1b7c8ae4c376491c5	vnchan	TRUE
27	00494aee7a0a40e1b7c8ae4c376491c5	nkakulla	FALSE
28	004d143185fd4fceb8ef53eb9dca1384	saithom	TRUE
29	004d143185fd4fceb8ef53eb9dca1384	sattili	FALSE
30	00544b4cc26f4a05995232dfeb9e5f63	mnallan	TRUE
Test

 

[Yongle]

I am shocked at how different that is from what you posted in your original workbook
(For the future) please be aware that posting rubbish sample data, that is not typical of your real data, is totally unhelpful to anyone trying to help you

Are TRUE & FALSE what you mean by Task1 and Task2 ?

 

[tirumal]

I thought that would make up for better understanding. Sorry for that. Learnt a lesson today.
Yes, True and false are the tasks.

 

[Yongle]

Ok - I think I now understand what you are struggling with

Must assign both TRUE and FALSE for any Job ID that is assigned
And
Each assignee must be assigned 5 X TRUE and 5 X FALSE

This is important:
Please confirm that column C always contains both TRUE and FALSE for every Job ID

 

[tirumal]

Yes, that is exactly what im trying to achieve.

True, column C always contains both TRUE and FALSE for every Job ID

 

[Yongle]

I need to test a few things
I will post suggestion tomorrow when back at my own PC