VBA to sort and pick data based on conflictng conditions

tirumal

Board Regular
Joined
Feb 16, 2020
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
-I receive different sets of data each day to sort and pick rows based on few conflicting conditions.

Here's what the data contains:
Column(A) - Job ID
Column(B) - Managers that have done the job.
Column(C) - Contains whether or not they've done the job (TRUE or FALSE).

Allocation test.xlsm
ABC
1Job IDManagerDislike Option
2490de5c8827c4392a33b916130c55e60snghojFALSE
3490de5c8827c4392a33b916130c55e60chatambaTRUE
48220df0bb10a4a12af3b381656a48d7cblbiFALSE
58220df0bb10a4a12af3b381656a48d7cchatambaTRUE
6de5e9277b7fc47a691e7400f3a4aa37bkuniarunFALSE
7de5e9277b7fc47a691e7400f3a4aa37bblbiTRUE
8e20eeec480404487a5b3f9724a36e41fabhyudFALSE
9e20eeec480404487a5b3f9724a36e41fpnrhulTRUE
10b114cfafb45a4a7c9ca24ce04f4d06b8snghojFALSE
11b114cfafb45a4a7c9ca24ce04f4d06b8rogeliosTRUE
1260dcb9a3fd804dc9924ee073b47468edblbiFALSE
1360dcb9a3fd804dc9924ee073b47468edsnghojTRUE
14387d3c1bee1145efb5518d9164ae33bablbiFALSE
15387d3c1bee1145efb5518d9164ae33bakuniarunTRUE
20th


*JOB ID:
- EVERY Job ID contains EXACTLY 2 lines
- EVERY Job ID contains one TRUE and one FALSE
- EVERY Job ID contains 2 Names. Although few Job IDs contains same manager for both TRUE and FALSE.

-There are around 35 - 40 managers in total and the data set has nearly 10000 rows of this data.
-Managers have TRUE and FALSE conflict for a job (Column C) and i need to pick few samples to audit these.

Rules for Macro:

- The sample data SHOULD include 60 Job IDs of both TRUE and FALSE selections of a manager.

- When a job ID is picked for manager's "TRUE" sample, the manager's data in "FALSE" column should also be picked. Keeping in mind his max. False data did not breach 60 threshold.

- 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).
Example:- 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.) for False can be accepted which will still make up for significant allocation of data.

What i've tried already:

VBA Code:
Option Explicit

Dim wf As WorksheetFunction
Dim ID As Range, Mgr As Range, Opt As Range
Dim A2 As Range, B2 As Range, C2 As Range
Dim ws2 As Worksheet, rng As Range, lastR As Long, r As Long
Dim count1 As Long, count2 As Long

Sub AllocateV3()
    Application.ScreenUpdating = False
    Set wf = Application.WorksheetFunction
'sort original data by job number first
    With Sheets("19th")
        .Sort.SortFields.Clear
        lastR = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A2:C" & lastR)
            .Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo
        End With
'add sheet
        .Copy after:=Sheets(Sheets.Count)
    End With
'set ranges in new sheet
    Set ws2 = Sheets(Sheets.Count)
    
    With ws2
        Set A2 = .Range("A:A")
        Set B2 = .Range("B:B")
        Set C2 = .Range("C:C")
    End With
'loop through values
    For r = 2 To lastR Step 2
        With ws2
            Set ID = .Cells(r, 1)
            Set Mgr = .Cells(r, 2)
            Set Opt = .Cells(r, 3)
            Set rng = .Cells(r, 1).Resize(2, 3)
            'ensure both elements of the job satisfy the condition
            If ID = ID.Offset(1) Then
                If wf.CountIfs(B2, Mgr, C2, Opt) > 60 And wf.CountIfs(B2, Mgr.Offset(1), C2, Opt.Offset(1)) > 60 Then rng.ClearContents
            Else
                GoTo Problem
            End If
        End With
    Next r
    On Error Resume Next        'do not remove this line - code fails if special cells finds nothing
    A2.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Exit Sub
Problem: MsgBox "Job " & ID & vbCr & "in row " & r, vbExclamation, "JOB ENTRY MISSING"

End Sub

Problem 1:
This code actually gives satisfying results given all conditions give above are met. But more of True data is allocated for few managers who have more percentage of "TRUE" in the entire data:

Allocation test.xlsm
HIJ
2Row LabelsTRUEFALSE
3kmarwvo11360
4kuniarun10360
5saithom7560
6chatamba7360
7pnrhul6360
8blbi6260
9chatuadi6260
10moumitad6260
11tulap6060
12sammula6060
19th (2)


Problem 2:
and More of False data significantly (>60) for very few managers whole likely have higher % of "FALSE" in entire data:

Allocation test.xlsm
HIJ
3bjjosh6084
4abhyuday6063
5moumitad6260
6wskh6060
7rdnsh6060
8abhyud3760
19th (2)


-Managers with least numbers in entire data are allocated 100% as mentioned in the above condition:

Allocation test.xlsm
HIJK
35nikumark4660106
36mamidih4160101
37abhyud376097
38kalarik346094
39sanikh174158
19th (2)


Only blockers are problem 1 and 2.

If anyone could solve this issue, i'd really appreciate it and would be of great help! Thanks in advance!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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
Top