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]

The code is failing on the first row
Is 9903 the correct value for the last row of data in new sheet?

If it is ....
Add a further 4 message boxes in the same place and let me know what all 6 message boxes say
MsgBox mT2 ,,"mT2"
MsgBox mF2 ,,"mF2"
MsgBox D2.Parent.Name & vbcr & D2.Address(0,0) ,,"D2"
MsgBox E2.Parent.Name & vbcr & E2.Address(0,0) ,,"E2"

I would expect the following values
2
9903
anukriti
abhyud
any sheet name and D2:D9904
any sheet name and E2:E9904

 

[tirumal]

Yes, the values are exactly the same!

2
9903
anukriti
abhyud
any sheet name and D2:D9904
any sheet name and E2:E9904

 

[Yongle]

Ah my apologies - copy\paste problem
The top of the code is missing 2 lines

Insert these 2 lines at the top of the module and replace the amended code with the original code

Option Explicit
Dim wf As WorksheetFunction

 

[tirumal]

It has created an output like this:

Allocation test.xlsm
	A	B	C	D	E	F	G
1	Job ID	TRUE	FALSE	TRUE	FALSE	Count TRUE	Count FALSE
2	886a1092bd7e42b7b8e28a9806ee7186	anukriti	abhyud	anukriti	abhyud	147	34
3	a2e94a0e10074d2b948f7c29377b7ddb	anukriti	abhyud	anukriti	abhyud	147	34
4	d8d20a671e0e4beeae283a06b2fe90c3	anukriti	abhyud	anukriti	abhyud	147	34
5	ef7062a43ead4d4d82f49a010e3261ab	anukriti	abhyud	anukriti	abhyud	147	34
6	4358995e376845dc80f86263fb4d5461	agangele	abhyud	agangele	abhyud	196	34
7	43e34098d347474b9decef6f631dd30c	agangele	abhyud	agangele	abhyud	196	34
8	61247bd1e62842368bf234c7fbb04d7a	agangele	abhyud	agangele	abhyud	196	34
9	9e5ecc4148714d33babd21d6d1cf4f6c	agangele	abhyud	agangele	abhyud	196	34
10	b40e8792f5f34a0c86e23613376c4ee0	agangele	abhyud	agangele	abhyud	196	34
11	d143b2500e2e4ebbbb9115df0c25645d	agangele	abhyud	agangele	abhyud	196	34
12	dc40567391264eecbccfdb5d36115058	agangele	abhyud	agangele	abhyud	196	34
13	e944726741184e1aa938a405f7fc2f42	agangele	abhyud	agangele	abhyud	196	34
14	eb91d2961077457d8103f11e33c0e086	agangele	abhyud	agangele	abhyud	196	34
15	058c1c8233504021abd40de72f1e235a	wajakhan	abhyud			294	34
16	13301447e24047bd8642c8d85f7c3abd	wajakhan	abhyud			294	34
17	1d75802b3f1343b09af8045bbb9a2644	wajakhan	abhyud	wajakhan	abhyud	294	34
18	1f48ef1eb11449318e78a17e0d48ed7c	wajakhan	abhyud	wajakhan	abhyud	294	34
19	5169d12ba01d42b9ad12cb45069a8021	wajakhan	abhyud	wajakhan	abhyud	294	34
20	55a2d2e633c54366b32a83097581c97d	wajakhan	abhyud	wajakhan	abhyud	294	34
16th (2)

I assumed i should be removing blanks. Removed and put a pivot. Oh boy, these are the results :D:

Allocation test.xlsm
	E	F	G	H
2	Managers	True Data	Managers	False Data
3	snghoj	320	nkakulla	272
4	biswadm	265	bjjosh	147
5	saithom	82	rdnsh	131
6	skaveesh	52	shprakha	82
7	alfrdav	50	saithom	55
8	pnrhul	50	alfrdav	51
9	wajakhan	50	blbi	50
10	bjjosh	50	skaveesh	50
11	agangele	50	pnrhul	50
12	shprakha	50	chatuadi	50
Sheet5

Also, people with lowest data are not allocated 100%:

Allocation test.xlsm
	E	F	G	H
34	estrb	45	estrb	45
35	mohaaimr	18	mohaaimr	43
36	abhyud	5	abhyud	32
37	rogelios	3	sammula	30
38	sammula	2	rogelios	3
39	Grand Total	2142	bippakay	1
40			Grand Total	2142
Sheet5

 

[Yongle]

remove apostrophe before

 E2.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

and blanks are removed

 

[tirumal]

Didn't notice that.
But the output is same as mentioned.

It has created an output like this:

Allocation test.xlsm
	A	B	C	D	E	F	G
1	Job ID	TRUE	FALSE	TRUE	FALSE	Count TRUE	Count FALSE
2	886a1092bd7e42b7b8e28a9806ee7186	anukriti	abhyud	anukriti	abhyud	147	34
3	a2e94a0e10074d2b948f7c29377b7ddb	anukriti	abhyud	anukriti	abhyud	147	34
4	d8d20a671e0e4beeae283a06b2fe90c3	anukriti	abhyud	anukriti	abhyud	147	34
5	ef7062a43ead4d4d82f49a010e3261ab	anukriti	abhyud	anukriti	abhyud	147	34
6	4358995e376845dc80f86263fb4d5461	agangele	abhyud	agangele	abhyud	196	34
7	43e34098d347474b9decef6f631dd30c	agangele	abhyud	agangele	abhyud	196	34
8	61247bd1e62842368bf234c7fbb04d7a	agangele	abhyud	agangele	abhyud	196	34
9	9e5ecc4148714d33babd21d6d1cf4f6c	agangele	abhyud	agangele	abhyud	196	34
10	b40e8792f5f34a0c86e23613376c4ee0	agangele	abhyud	agangele	abhyud	196	34
11	d143b2500e2e4ebbbb9115df0c25645d	agangele	abhyud	agangele	abhyud	196	34
12	dc40567391264eecbccfdb5d36115058	agangele	abhyud	agangele	abhyud	196	34
13	e944726741184e1aa938a405f7fc2f42	agangele	abhyud	agangele	abhyud	196	34
14	eb91d2961077457d8103f11e33c0e086	agangele	abhyud	agangele	abhyud	196	34
15	058c1c8233504021abd40de72f1e235a	wajakhan	abhyud			294	34
16	13301447e24047bd8642c8d85f7c3abd	wajakhan	abhyud			294	34
17	1d75802b3f1343b09af8045bbb9a2644	wajakhan	abhyud	wajakhan	abhyud	294	34
18	1f48ef1eb11449318e78a17e0d48ed7c	wajakhan	abhyud	wajakhan	abhyud	294	34
19	5169d12ba01d42b9ad12cb45069a8021	wajakhan	abhyud	wajakhan	abhyud	294	34
20	55a2d2e633c54366b32a83097581c97d	wajakhan	abhyud	wajakhan	abhyud	294	34
16th (2)

I assumed i should be removing blanks. Removed and put a pivot. Oh boy, these are the results :D:

Allocation test.xlsm
	E	F	G	H
2	Managers	True Data	Managers	False Data
3	snghoj	320	nkakulla	272
4	biswadm	265	bjjosh	147
5	saithom	82	rdnsh	131
6	skaveesh	52	shprakha	82
7	alfrdav	50	saithom	55
8	pnrhul	50	alfrdav	51
9	wajakhan	50	blbi	50
10	bjjosh	50	skaveesh	50
11	agangele	50	pnrhul	50
12	shprakha	50	chatuadi	50
Sheet5

Also, people with lowest data are not allocated 100%:

Allocation test.xlsm
	E	F	G	H
34	estrb	45	estrb	45
35	mohaaimr	18	mohaaimr	43
36	abhyud	5	abhyud	32
37	rogelios	3	sammula	30
38	sammula	2	rogelios	3
39	Grand Total	2142	bippakay	1
40			Grand Total	2142
Sheet5

 

[Yongle]

The code is now working - now I need to teach you how to write rules to get what you want. I will help you to get started

ALL entries are placed in the new sheet - use successive rules to delete more and more of what you want to delete, making sure that you never delete something that you want to retain

RULE 1
people with lowest data are not allocated 100%:
This seems to be very important to you
How do you define "lowest" ?
- eg all alloactions of TRUE & FALSE below 30 should be retained

Tell me in words what RULE1 should be

 

[tirumal]

I will set out the rules which will include RULE 1 as well:
-I receive different sets of data each day to sort out this way. The numbers for each person is different each day but the format is the same. The ones with high and low numbers of True and False might remain on the same levels of data. By levels i mean, high/low numbers of true/false.
-Given that, i need to allocate 60 from each manager's data.
-*RULE 1* Some managers have significantly very fewer data of either True (or) Both True and False (<60). In that case, i need to allocate their whole data which is the only data they have (keeping in mind that the cap is =60).
Ex.:- manager1 has 14 True data in original file and 32 False. All his job ID's should be selected. (14 True and 32 False).
Manager2 has 30 True data and 80 False data. In such case, All of his True data should be selected and False should be = the max. cap (60).
*This is very important because i can't eliminate the ONLY numbers of data he has as everyone else has to allocated at least and =60.
-Now 60 for both True and False might not be statistically achieved given the conditions. In that case, a margin of + 5 or upto +10 (max.) for True and + (or) 5 or upto -10 (max.) False can be accepted which will still make up for significant data.

 

[Yongle]

Post#88 requirement is so different to what was in your first post that I have probably approached this in the wrong manner from the start
- I am going round in circles getting nowhere
- it neeeds a new pair of eyes

I suggest you start a new thread, using the details in post88 as your FIRST post and include sample data perhaps using XL2BB
Thread Title possibly VBA to pick data based on conflictng conditions
Linking back to this thread would be unhelpful to anyone trying to help you

I withdraw from this thread
Good luck

 

[tirumal]

Post#88 requirement is so different to what was in your first post that I have probably approached this in the wrong manner from the start
- I am going round in circles getting nowhere
- it neeeds a new pair of eyes

I suggest you start a new thread, using the details in post88 as your FIRST post and include sample data perhaps using XL2BB
Thread Title possibly VBA to pick data based on conflictng conditions
Linking back to this thread would be unhelpful to anyone trying to help you

I withdraw from this thread
Good luck

Thanks for you patience and help