Help with macro filter sequencing

[davidgahan]

Hey everyone,

I'm a beginner to VBAs. Currently I started to learn myself just by recording macros and then going through all the lines and finding out what actually does what. In my company, there is one excel file that comes on daily basis (for past 10 years) and needs to be adjusted in order to get out some data. As an efficiency junkie, I of course tried to make a macro out of the file to simplify the work. Nevertheless, there are some things I struggle with. Could one of you try to record such macro, edit it and send it over, so I can review it and learn some more? I hope it wouldn't take too long to make, and I would very much appreciate it.

Now to the topic. I'm attaching and excel file with dummy data filled (the legend is actual). The data in green columns are the only one I am interested in.

First I need to create a new sheet with 5 locations (Column O, DeliveryDepotCity) each location having 3 cells (ATA, ETA and Adjusted ATA). That's the easy part, these 5x3 numbers are the numbers I'm looking for in the end.

Now this is where I struggle: In order to get the numbers, various filters need to be applied. Instructions as following:

1. from containerID - filter everything that does not contain "part"
2. In AdjPlannedDelivery - untick blanks, untick everything up to today (included)
3. in PlannedDelivery - delete all visible cells
4. in AdjPlannedDelivery - unfilter
(Steps 1-4 are done only once, upcoming steps are then repeated for every DeliveryDepotcity)
5. Select location from deliverydepotcity
6. in planneddelivery - untick all up till today (included)
7. count number of cells with data in ATA and note it down to the sheet we created in the beginning.
8. count number of cells with data in ETA and note it down to the sheet we created in the beginning.
9. In ATA - untick blanks and untick past 4 days
10. count number of cells with data in the adjusted ATA and note it down to the sheet we created in the beginning.

Once again, any advice or help is very much appreciated.

Thanks a lot,

David

[Paul_Hossler]

Really not sure about some of your business rules, but here's my best guess, and a different way to consider

Instead of filtering, I added a "Report" helper column to sieve the raw data and a pivot table to report the results


Like I said, really not sure if I got the rules correct, but IMHO the sieve approach would be easier

 
Option Explicit

Const cATA As Long = 1
Const cETA As Long = 2
Const cContainerID As Long = 5
Const cPlanned As Long = 13
Const cAdj As Long = 14
Const cCity As Long = 15
Const cReport As Long = 21

Sub ReportData()
    Dim iRow As Long
    Dim rData As Range
    
    Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
    
    With rData
        For iRow = 2 To .Rows.Count
            
            '1. from containerID - filter everything that does not contain "part"
            If InStr(LCase(.Cells(iRow, cContainerID).Value), "part") > 0 Then
                .Cells(iRow, cReport).Value = "No#1"
 
            '2. In AdjPlannedDelivery - untick blanks
            ElseIf Len(.Cells(iRow, cAdj).Value) = 0 Then
                .Cells(iRow, cReport).Value = "No#2"
            '2. In AdjPlannedDelivery - untick everything up to today (included)
            ElseIf .Cells(iRow, cAdj).Value = Int(Now) Then
                .Cells(iRow, cReport).Value = "No#3"
            
            '3. in PlannedDelivery - delete all visible cells - not needed
            '4. in AdjPlannedDelivery - unfilter - not needed
            '5. Select location from deliverydepotcity - not needed
            
            '6. in planned delivery - untick all up till today (included)
            ElseIf .Cells(iRow, cPlanned).Value < Int(Now) Then
                .Cells(iRow, cReport).Value = "No#4"
            
            '7. count number of cells with data in ATA and note it down to the sheet we created in the beginning -  in pivot table
            '8. count number of cells with data in ETA and note it down to the sheet we created in the beginning -  in pivot table
            
            '9. In ATA - untick blanks
            ElseIf Len(.Cells(iRow, cATA).Value) > 0 Then
                .Cells(iRow, cReport).Value = "Yes4"
            
            '9. In ATA - untick past 4 days
            ElseIf .Cells(iRow, cATA).Value < Int(Now) - 4 Then
                .Cells(iRow, cReport).Value = "Yes4"
            
            Else
                .Cells(iRow, cReport).Value = "Yes"
            End If
            
            '10. count number of cells with data in the adjusted ATA and note it down to the sheet we created in the beginning - in pivot table
        
        Next iRow
    
    End With
    
    ActiveSheet.Cells(1, 1).CurrentRegion.Name = "AllData"
    
End Sub

Edits

1. You could use VBA arrays, but for something this small, I'd just stick to worksheets
2. Instead of the PT, it might be possible to use some clever worksheet function on the reporting sheet after the raw data has been processes. I went with a PT since it was easier (for me)

[SamT]

I'm a beginner to VBAs

First: Your statement of "HOW" to do what you want is probably not the best way to programmatically accomplish "WHAT" you want to accomplish.

How about you let us know "WHAT" you want the results to be?

[davidgahan]

Thank you Paul and Sam for your replies.

Paul: I tried to apply your reporting way of sieving through the data, yet I haven't managed to figure out the right way to sieve through the data. I'm gonna attach a file from today (actual file) that has been cleared of all the informative data - the settings of the task remains the same. I tried to visualise such through PT, unsuccessfully to find the correct numbers.

Sam: Thank you for your suggestion. My goal is to find out how many products (one row is one product) are in the warehouse and how many are on the way to the warehouse (ATA and ETA) for each of the cities. There are certain rows I need to not consider (the "part" containers-). I need to not consider the rows that had planned delivery set for sometimes in the past (and today), yet had been rescheduled to somewhere in the future (tomorrow on); the shuffle with planned delivery and adjplanned delivery.

Simply put: What am I trying to find out: Number of products for each city for a certain day, with specific filters.

Once again, thank you so much to both for the tips!
Dummy list.xlsx
David

[Paul_Hossler]

Adding a guess onto my first guess:

Your business rules are still not 100% clear to me, so I added a 'Reason' column to the data sheet

I would really help if you could post the answers that you're expecting, even if you have to generate then manually

[davidgahan]

Hey Paul,

the reasons is something I didn't think of, yet i'm not sure how to deal with it if multiple rules are valid at the same time.

I'm attaching a file from tomorrow, with the numbers that should come out as a solution. Note, that date that I specified as "today" will be then considered as 15.12.2017. Also Rose City is divided into A and B, yet for the final result, they're both taken together.Dummy list_15122017.xlsm

I really appreciate your help Paul,

Thank you very much!

Looking forward to your answer,

David

[Paul_Hossler]

What is ATA2 in F1?

What are it's rules?

[Paul_Hossler]

Working on this off and on a little

Changed approach. Instead of pivot table to report, I used a user defined function that takes the data range and a city as input, and returns 1Rx3C array to worksheet

Tried to follow your logic and loaded data into VBA array since I don't like to change original data

Since the number of ATA and ETA is what you want, if a line is 'thrown out' I just clear ATA and ETA for that line

1. Make all date/times into just date part
2. Clear ATA/ETA if 'part' or not input city (with the (A) and (B) adjustment)
3. Clear Planned Delivery if Adjusted=blank or before Today (fixed for this data set at 15 Dec 2017)
4. Clear ATA and ETA if Planned > Today (15 Dec 2017)
5a. Count ATA and ETA
5b. count ATA and ETA if ATA <= 11 Dec 2017

However, some of the data doesn't match -- Oak is 100%, rest are close

Capture.JPG

If you can verify that your counts are correct and your rules are correct, I'll investigate the macro some more

[davidgahan]

Hello Paul,

looks more promising!

I've rechecked my result numbers and yes - there was indeed a mistake (typo of 87 instead of 86).. Your result of Rose City is the correct one.
There is one rule I didn't mention: For rose city, the step mentioned by you (5b. count ATA and ETA if ATA <= 11 Dec 2017) isn't 11 Dec (like for the other cities) but it's 12 Dec.

Bamboo city - 11 Dec
Palm city - 11 Dec
Rose city - 12 Dec
Oak city - 11 Dec


The rest of rules are also correct. Also I suppose step 11. would be "unfilter ATA, unfilter planned delivery, unfilted location, repeat steps 5-11"


Conclusion:
Your results for Rose and Oak are 100% correct based on my rulses.
The results for Bamboo and Palm are not, there is a mismatch somewhere.

This approach would do exactly what I need it to do :-))

Once again, huge thank you for the help Paul.

Have a wonderful day,
David

[Paul_Hossler]

OK, still using 15 Dec as the AsOf date

With the correction, Rose city ATA2 matches

Think this should do it for you

I must have changed some of the data I was using and that why some numbers were off