Automatic Filter Drill Down

[Dmak]

Hi there,

Hope everyone is doing well.

I am trying to create a macro which takes the value of a cell (based on the condition of it being "SEMI", "RAW", "PKG"). I then take the BOM No. which is in the first cell of the same column with the cell condition and use this cell value to filter column "E". (NO.) I do this for every condition met until there is no more condition to check for.

There are a lot more rows then this (35K Plus so I could not use the XLBB Tool).

Here is what the sheet looks like:

Basically, I am currently at the stage of using a FOR EACH loop that checks each if cell in column1 = "SEMI" or "PKG" or "RAW".... then it would select the Production BOM .NO (which in this case is CK00049) and use this cell value to filter column "E" (NO.) which would drill down the data, This loop will repeat again onto the next stage until a single "FG" is returned which then the value of the 'FG' could be returned into "RESULTS". Then I wish to reset the filter and move onto the next SEMI found.

On the second step of the drill down it would something like this:

As you can see: the bottom two rows (C:38165, C:38286) are tagged as semi, so I would have to again use these cell value and filter column "E" to drill down further until the final is returned. Using C:38165 this would be returned:

After this, I would copy and paste the first cell as a result, and then would drill back out to repeat with C:38286 (if not more). IF not the case, I would reset the whole thing, with the initial filter, and move onto to the next condition of it being "SEMI" or "RAW" or "PKG" and etc.


I understand that this will be a nested for each loop with multiple stages and with some complicated conditions, but I cant seem to figure if its even possible to print out all the conditional results in one go.


Here is the structure of what I have so far:

First search to see if product type condition is met.

if true: then take first cell in active column and use cell value as filter for column "E" (NO.)
then run search condition again
if it was false here, open new sheet with results
if true take cell again and filter down




if false: return "No queries found" end if and go to end code

then
run search condition again

if true take cell again and filter down

if anyone can point me in the right direction or create a piece of code that can loop at least once, it will be greatly appreciated.

I am pretty stuck with this so please help out!

 

[Dmak]

by next match you meant the next SEMI which has BOM no CK00072?
and repeat the process as the first SEMI before?
and after that do it until the last SEMI?

I think I understand the main requirement. But actually, I was hoping you can provide a sample that hasn't filtered yet, so I can test it easier. And also I probably will use an array not filter to do it.
So could you upload your workbook with not filtered data (and please remove sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.

Also yes, you would move down to CK00072 all and then repeat the cycle

 

[Akuini]

Can we do the process backward?
Something like this:

1. Get col NO. (one by one ) in every row that has FG
2. For example, say row 2 is:
1000017-1 DI00012 FG

Production BOM No.	No.	Column1
1000017-1	DI00012	FG
1000017-1	PK00404	FG
1000017-1	PK00070	FG

3. Then we search DI00012 in col A, see if there are rows with "SEMI" "RAW" OR "PKG",
if there are, then row 2 is the final product. So we copy row 2 to another sheet.
4. Do the same process for all other rows with FG.

 

[Dmak]

Can we do the process backward?
Something like this:

1. Get col NO. (one by one ) in every row that has FG
2. For example, say row 2 is:
1000017-1 DI00012 FG

Production BOM No.	No.	Column1
1000017-1	DI00012	FG
1000017-1	PK00404	FG
1000017-1	PK00070	FG

3. Then we search DI00012 in col A, see if there are rows with "SEMI" "RAW" OR "PKG",
if there are, then row 2 is the final product. So we copy row 2 to another sheet.
4. Do the same process for all other rows with FG.

So about the COL No. I would already know what to look for. For example, I would pull up the sheet from the system. Then I would set my initial filter. (Like DI00069 in the example, but this changes so it could be a input variable). AND Then I would start going down the list to look for either SEMI, RAW, OR PKG, and then drill down and etc.

Essentially the NO. is a product, all I need to do is find out what it feeds into.

I think what you're saying makes sense. If we can work in a way that is more efficient, id say go for it!

 

[Akuini]

So about the COL No. I would already know what to look for. For example, I would pull up the sheet from the system. Then I would set my initial filter. (Like DI00069 in the example, but this changes so it could be a input variable).

Are you saying you need only one number (for example DI00069) at a time to be checked?
What I suggested will generates result of all number that meets the criteria as final product. And I won't use filter because it would be slow, I will use array & dictionary to do it.

 

[Dmak]

Are you saying you need only one number (for example DI00069) at a time to be checked?
What I suggested will generates result of all number that meets the criteria as final product. And I won't use filter because it would be slow, I will use array & dictionary to do it.

Yes, is there any way we can first filter the data before with the criteria, then use array & dictionary? This way, we can return all the of the FG based on the one NO.

If not, what I can do is filter the data first then copy it into another sheet and then work backwards?

I don't want to complicate this anymore for you, please feel free to create your own efficient method of drilling down and Ill gladly make it my way of use!

 

[Akuini]

Yes, is there any way we can first filter the data before with the criteria, then use array & dictionary? This way, we can return all the of the FG based on the one NO.

I'm really sorry, I can't find a working solution for this, it's much harder than I thought.
The multi values & multi stage searching makes it hard to find the final product.
I hope some experts here would step in. It might need a recursive function which is beyond my knowledge.

 

[Dmak]

No worries at all my friend. Ill try to come up with an alternative.

 

[Akuini]

@Dmak
I think I get it now.
Try this;
1. Put the number you want to search in cell M1
2. Run Sub a1178526a
3. Output is in sheet Result

Option Explicit
Dim va, vb, vc, z As Long, h As Long
Sub a1178526a()
'https://www.mrexcel.com/board/threads/automatic-filter-drill-down.1178526/
Dim t
Dim d As Object
Dim i As Long, j As Long, ra As Long, rb As Long
Dim xa As String

t = Timer
'Sheets("Data").Activate
'Data is sorted ascending by col J (all rows with FG must be on top)
With ActiveSheet.ListObjects("Table1").Range
    .Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlYes
End With


'find last row with FG
ra = ActiveSheet.Range("J:J").Find(What:="FG", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row

'find last row of data
rb = ActiveSheet.Range("J:J").Find(What:="*", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row

'populate all rows with FG to va
va = Range("A2:J" & ra)
''populate all rows with not FG to vb
vb = Range("A" & ra + 1 & ":" & "E" & rb)
ReDim vc(1 To UBound(va, 1), 1 To UBound(va, 2))

z = 0
h = 0
xa = Range("M1")
Call to_Recursive(xa)

'OUTPUT
With Sheets("Result")
    .Cells.ClearContents
    .Range("A1").Resize(UBound(va, 1), UBound(va, 2)) = vc
End With

Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub


Sub to_Recursive(xn As String)
Dim flag As Boolean, xGo As Boolean
Dim k As Long, i As Long, j As Long
Dim Q As String
    
flag = False

For i = 2 To UBound(vb, 1)
    
    If vb(i, 5) = xn Then
            If vb(i, 1) <> Empty Then
'                Debug.Print i & " : " & vb(i, 5) & " : " & vb(i, 1)
                z = i
                Call to_Recursive(CStr(vb(i, 1)))
            End If

        flag = True
    End If

Next

If flag = False Then
    If z > 0 Then
        If vb(z, 5) <> "" Then
'        Debug.Print "end : " & z & " : " & vb(z, 5) & " : " & vb(z, 1)
            xGo = False
            Q = vb(z, 1)
    
            For j = 2 To UBound(va, 1)
                
                If va(j, 5) = Q Then
                    h = h + 1
                    For k = 1 To 10
                        vc(h, k) = va(j, k)
                    Next
                    xGo = True
                End If
                
                If xGo = True Then Exit For
    
            Next
            
            vb(z, 5) = ""
            vb(z, 1) = ""
        End If
    Else
        'MsgBox "Can't find :" & xn
    
    End If

End If
End Sub

The workbook:

Dmak - automatic-filter-drill-down 2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[Akuini]

The code in post #18 is flawed.
Use this one instead:

Option Explicit
Dim va, vb, vc, z As Long, h As Long
Sub a1178526a()
'https://www.mrexcel.com/board/threads/automatic-filter-drill-down.1178526/
Dim t
Dim d As Object
Dim i As Long, j As Long, ra As Long, rb As Long
Dim xa As String

t = Timer
'Sheets("Data").Activate
'Data is sorted ascending by col J (all rows with FG must be on top)
With ActiveSheet.ListObjects("Table1").Range
    .Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlYes
End With


'find last row with FG
ra = ActiveSheet.Range("J:J").Find(What:="FG", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row

'find last row of data
rb = ActiveSheet.Range("J:J").Find(What:="*", LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row

'populate all rows with FG to va
va = Range("A2:J" & ra)
''populate all rows with not FG to vb
vb = Range("A" & ra + 1 & ":" & "E" & rb)
ReDim vc(1 To UBound(va, 1), 1 To UBound(va, 2))

z = 0
h = 0
xa = Range("M1")
Call to_Recursive(xa)

'OUTPUT
With Sheets("Result")
    .Cells.ClearContents
    .Range("A1") = "NUMBER : " & xa
    .Range("A2").Resize(UBound(va, 1), UBound(va, 2)) = vc
End With

If vc(1, 1) = "" Then MsgBox "Can't find : " & xa
Debug.Print "It's done in: " & Timer - t & " seconds"

End Sub


Sub to_Recursive(xn As String)
Dim flag As Boolean, xGo As Boolean
Dim k As Long, i As Long, j As Long
Dim Q As String
   
flag = False

For i = 2 To UBound(vb, 1)
   
    If vb(i, 5) = xn Then
            If vb(i, 1) <> Empty Then
'                Debug.Print i & " : " & vb(i, 5) & " : " & vb(i, 1)
                z = i
                Call to_Recursive(CStr(vb(i, 1)))
            End If

        flag = True
    End If

Next

If flag = False Then
    If z > 0 Then
        If vb(z, 5) <> "" Then
'        Debug.Print "end : " & z & " : " & vb(z, 5) & " : " & vb(z, 1)
            Q = vb(z, 1)
   
            For j = 2 To UBound(va, 1)
               
                If va(j, 5) = Q Then
                    h = h + 1
                    For k = 1 To 10
                        vc(h, k) = va(j, k)
                    Next
               
                End If
               
            Next
           
            vb(z, 5) = ""
            vb(z, 1) = ""
        End If
    Else
'        MsgBox "Can't find : " & xn
   
    End If

End If
End Sub

 

[Dmak]

Hey Akuini, Thanks so much for your post!!!!

could you post just the excel file from drop box or google drive? I cannot extract a rar file on my work computer. If you can do that that will awesome!!