VBA code to pick data based on multiple conditions

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
Debug.Print writes to the Immediate Window
It is a useful tool when trying to see what is going on with variables and values etc, especially when looping through many cells
The Immediate Window is displayed (when in VBA editor) using shortcut {CTRL} g or by clicking on menu tab View and then Immediate Window

To return items being rejected due to either count of items exceeding 5
... remove the apostrophe before these 2 lines in the latest version of the code
VBA Code:
                        'Debug.Print "failed CountIf",r, ID, Mgr1, Opt1
                        'Debug.Print "failed CountIf",r, ID2, Mgr2, Opt2
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

tirumal

Board Regular
Joined
Feb 16, 2020
Messages
50
Office Version
2016
Platform
Windows
Hi, Thanks for getting back :)
Your code is doing wonders as per allocation.
I've changed the number from maximum number 5 to 49 and ran the code on whole data.
The result is coming out as desired for most of the cases. But i'm not sure why this is happening:

Please check the yellow and red marks (E and F columns contain entire original True and false data as per manager):

Allocation test.xlsm
ABCDEF
30mtshah492675512440
31nikumark49499878512
32panchada494998122400
33pnrhul494998175170
34rdnsh1523381523
35rogelios61218612
36saithom494796368261
37sanikh494998454389
38sattili494998184233
39shprakha494998110324
40siaditi49499887413
41skaveesh493988430445
42smantral494998136147
43snghoj494998986372
44sshaka494998395278
45tulap494998175217
46tvinaya11112111
47vnchan494998228157
48wajakhan494998256355
49Grand Total1928193438621190511905
Sheet4


- The true and false are not equally distributed in the end and people marked in yellow have a huge variance b/w true and false where false is much lower. False data cannot be lower than true data when there's enough samples in the original data right?
- Is this because of any data limitation ?
or the value 49 i've put ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
The code is working correctly - but is working through JOB by JOB
By cancelling it after 50 you get the first found 50 jobs
In the meantime run the sub without your 50 restriction
I have an idea - will update thread shortly
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
Sorry - I just realised I misunderstood what you said last time - ignore previous reply
 

tirumal

Board Regular
Joined
Feb 16, 2020
Messages
50
Office Version
2016
Platform
Windows
Reason for putting 50 is because i need the data to be allocated for same (50-60) or more sometimes.
The code is running perfectly but
The true and false allocation itself is not equal. Also False data (Column C) is lower for some people.

Allocation test.xlsm
ABCD
39saithom504898
40ayumishr504191
41skaveesh504090
42biswadm503585
43elisaty 323466
44mtshah502777
45rdnsh152338
46rogelios61218
47tvinaya11112
48minhaza11
49Grand Total196619723938
Sheet6


I'm guessing if it's data limitation ?
Will putting extra data help?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
I am currently testing something that may help you
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
I have found an approach that may work and will update the thread within the next 2 hours after I have fully tested revised code
 

tirumal

Board Regular
Joined
Feb 16, 2020
Messages
50
Office Version
2016
Platform
Windows
I have found an approach that may work and will update the thread within the next 2 hours after I have fully tested revised code
Sure! Will be looking forward to the same.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,267
Office Version
365
Platform
Windows
The code ran much too slowly when tested
I will revert when I have resolved that issue
 

Forum statistics

Threads
1,089,637
Messages
5,409,464
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top