New work project: stuck on Multidimensional Array / Directory

[tweedi]

Hi All!

I have a database storing all open files and at which phase they currently are (each phase representing a different step in the process e.g Registered / Confirmed / Production / Test / Certified / Dispatched

The aim is to detect when a file goes reach a certain phase and then would be rejected to the previous phase. I attached a simplified example of the database with only 2 open files (they are in reality hundreds of file, the excel spreadsheet has tens of thousands of rows)

Therefore I could summarize in a different sheet the files that reached phase 4 and was rejected to phase 3 (ideally with the date when it was back at phase 3.

Apparently the best way (as I cannot loop it would take forever and crash) is to use arrays and directory but I am a bit stuck here as the values should be stored by pair I think (File ID + Current Status).

Can anyone help with this?
Many Thanks!!

File:

Day Count	Date	File ID	Description	File Status
1	26/08/2015	800	Accident	Phase 3
1	26/08/2015	801	Theft	Phase 3
2	27/08/2015	800	Accident	Phase 3
2	27/08/2015	801	Theft	Phase 3
3	28/08/2015	800	Accident	Phase 3
3	28/08/2015	801	Theft	Phase 3
4	29/08/2015	800	Accident	Phase 4
4	29/08/2015	801	Theft	Phase 3
5	30/08/2015	800	Accident	Phase 3
5	30/08/2015	801	Theft	Phase 3
6	31/08/2015	800	Accident	Phase 3
6	31/08/2015	801	Theft	Phase 4
7	01/09/2015	800	Accident	Phase 3
7	01/09/2015	801	Theft	Phase 3
8	02/09/2015	800	Accident	Phase 3
8	02/09/2015	801	Theft	Phase 4
9	03/09/2015	800	Accident	Phase 3
9	03/09/2015	801	Theft	Phase 3
10	04/09/2015	800	Accident	Phase 3
10	04/09/2015	801	Theft	Phase 3

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[dmt32]

Hi,
couple of suggestions:

1 - you could read your table into an array & use this to filter out the record records which could be copied to another sheet - as an example based on your table:

Sub aa()
    Dim i As Integer, msg As Integer, FileID As Integer
    Dim RangeData As Variant
    Dim FileStatus As String
    
    FileStatus = "Phase 4"
    
    FileID = 801
    
    With ThisWorkbook.Worksheets("Sheet1")
        'intialize array with data from table
        RangeData = .Range("A2:E" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With




    For i = LBound(RangeData, 1) To UBound(RangeData, 1)
    
    If RangeData(i, 3) = FileID And RangeData(i, 5) = FileStatus Then
    
    
      msg = MsgBox("Day Count: " & RangeData(i, 1) & Chr(10) & _
                "           Date: " & RangeData(i, 2) & Chr(10) & _
                "         File ID: " & RangeData(i, 3) & Chr(10) & _
                "Description: " & RangeData(i, 4) & Chr(10) & _
                "   File Status: " & RangeData(i, 5), 36, "Array Demo")
            If msg = 7 Then Exit Sub
        
    End If
    
    Next i


End Sub

This would display the msgbox for only those records that equal FileID 801 & FileStatus Phase 4

2 - if your data set is very large, you could consider using advanced filter which probably would be a lot quicker. You would have a criteria sheet to set the filter allowing user to specify which records are required & result can either be filtered in place or copied to another sheet.

If not used advanced filter before have a look here for guidance: http://www.contextures.com/xladvfilter01.html

Hope helpful

Dave