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

MartinF1984

New Member
Joined
Feb 18, 2020
Messages
2
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
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!

1582084882046.png
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

MartinF1984

New Member
Joined
Feb 18, 2020
Messages
2
Office Version
  1. 2016
  2. 2011
Platform
  1. Windows
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

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

VBA 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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

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