How to collate data in a single spreadsheet based on 4 criterias

[MartinF1984]

Hi all,

New member here.
I have a spreadsheet with the below format. So a single workbook would have 90-100 sheets with that same format.
What I want to do is to collate all the data that match 4 criterias in a single workbook: Date, Oversees Manager, Overall Status, Assigned Contractor.
For example, I want to know the Project Description and Budgets from 05-May-2020 up to 02-June-2020 with the Overseas Manager: Greg, Overall Status: Ongoing and Assigned Contractor: Yokohama. There are 8 different Overseas Manager, 5 Overall Status Options and 10 different Contractors.

Please help me automate this process - as sorting through 100 worksheets for the previous years takes a very long time. The date range for each worksheet also changes - so some worksheets wont have any dates for January or April etc

Thank you so much for your assistance!

 

[CountTepes]

What is your coding experience?

 

[MartinF1984]

Hi CountTepes, Very low actually - although I think I’m familiar with for loops and if loops and that they might be useful for this case ?

 

[CountTepes]

I've put together something for you to get started. It will look OK as well.

Create a new workbook.

Go to the code section and insert a userform (Rightclick on the project and select insert-userform)

Put on a textbox for each of your search criteria. Upperdate. lowerdate, Manager, etc. Label and Name them so that you know what you are referencing, further down in the code.

add a commandbutton and this is the only place you need to code the form. Doubleclick the button, after you have created it and it will take you to the code for the click event for the form.
Type in userform1.hide If you have named your form as anything else, then use the name not userform1

Go to the workbook code area and insert the following starter code.

Private Sub ReadData()
Dim FName As String

Dim InFile As Workbook
Dim OutFile As Workbook
Dim InSh As Worksheet
Dim OutSh As Worksheet

Dim InRow as Long
Dim OutRow as Long
DimDateRow as long                        ' Need to loop through each date on the sheet
Dim Flag as boolean

FName = Application.GetOpenFilename("Excel Files, *.xls*") ' This will display a file open dialogue box, showing only excel files. You can navigate to the workbook you want to analyse and open it.
If FName = "False" Then Exit Sub    ' It returns "False", if you cancel.
Workbooks.Open (FName)             ' This opens the workbook
Set InFile = ActiveWorkbook          'and sets a variable of type workbook to the open workbook

Workbooks.Add                              ' This creates a new workbook where you are going to add your results
Set OutFile = ActiveWorkbook       ' Set a variable to the new workbook
Set OutSh = ActiveSheet                ' and set a variable to the active worksheet in the new workbook. This is where the data is going to go.
UserForm1.Show                            ' Now open the userform, you created previously and populate the fields.
OutRow=1
DateRow=6                                     ' The starting row for storing your data. You will need to increment every time you have written a row.
For Each InSh In InFile.Sheets        ' This will go through each sheet in the workbook where your data is.
       Do Until isempty(InSh.cells(DateRow,1))  ' This checks if the date cell is empty
            Flag=True                                ' This will be used later. Explained below code.

             '    This is where you need to put the code to look into your sheets
           DateRow=DateRow+1             ' When referencing the Date, always access using DateRow not a fixed number for the row.
       Loop
Next
End Sub

You know the layout of your data, so compare what you have stored in your userform fields, against the data on the sheet.
You reference the input data as: InSh.cells("A2") for the manager.
I find that the best way of checking for several conditions all being true is to use logic statements, rather than if then else statment. It will make the code simpler to read.

If a userform text box is empty, then treat it as any value.

The test logic would be:
Flag= Flag and ( InSh.Cells(2,1)<>UserForm1.ManagerBox.Text or Userform1.ManagerBox.Text="")
and so on for each of the criteria.
You can use flag=flag and criteria1 and criteria 2 and criteria 3, but it will look confusing and messy. Also when you are debugging, you can step each line which will show where it is going wrong.
When you get to the end, If all your criteria are true, then the flag will be true.

Then all you need is:

If Flag Then
OutSh.Cells(OutRow,WhateverColumnYouWantIt).Text=InSh.cells(2,1).Text ' and so on for each value to copy.

End if

I tried to keep it simple and but not do everything for you. As you fill in the blanks, you will learn a lot more. Especially when debugging.
Let me know how you got on and come back if you get stuck.