Hello,
I need some serious help. I am trying to track cleaning crews who come and go, cleaning buildings on an as needed basis. The buildings are identified by contract ID, address, and sector. The crews are tracked by date, the day of week they work and the hour of day they work. For example, team 12 works Sunday thru Wednesday (day 1 thru 4), from hour 6 through 17.
The data tab is what I receive on Monday. The Rules tab identifies the day of week range, the day of the week range, and in column A is the place that I will paste the hour of day that a team works.
Initially I created a tab (HOD) to filter out teams from the DATA tab that didn’t work from hours 6 through 17. Then I was going to create a tab (not shown) to filter out teams from the data in tab HOD that didn’t work day 1 through 4. And, finally, I was going to create a tab (Team) to filter the data from the not created tab of just team 12 (who worked days 1-4, and hours 6-17).
Tab TEAM is where I combined the three formulas into one and failed.
So here is what I need: From the data in the DATA tab, I need a formula that will list in a new tab team 12 when they work hours 6-12, and days 1-4. I hope you will see my thinking in tab HOD.
Here is the formula I came up with that did not work:
=IF(COUNTIF(RULES!$A$2:$A$13,DATA!$G2),DATA!A2,IF(COUNTIF(RULES!$H$2:$H$5,DATA!$E2),DATA!A2,IF(DATA!$H2=12,DATA!A2,"")))
=IF(COUNTIF(RULES!$A$2:$A$13,DATA!$G2) ),DATA!A2, == this part filters out the data for those that didn’t work during those hours.
IF(COUNTIF(RULES!$H$2:$H$5,DATA!$E2),DATA!A2 == this part is supposed to filter out the data for those who didn’t work days 1-4.
IF(DATA!$H2=12,DATA!A2,""))) == this part is supposed to filter out the data from all teams except team 12.
I can do each in a separate tab for each filter to get the results, the problem is the data spans about 75,000 lines and 40 columns and takes forever to run (Office 365 Ugh).
If I could attach the spreadsheet I would so you could see it.
Help!
I need some serious help. I am trying to track cleaning crews who come and go, cleaning buildings on an as needed basis. The buildings are identified by contract ID, address, and sector. The crews are tracked by date, the day of week they work and the hour of day they work. For example, team 12 works Sunday thru Wednesday (day 1 thru 4), from hour 6 through 17.
The data tab is what I receive on Monday. The Rules tab identifies the day of week range, the day of the week range, and in column A is the place that I will paste the hour of day that a team works.
Initially I created a tab (HOD) to filter out teams from the DATA tab that didn’t work from hours 6 through 17. Then I was going to create a tab (not shown) to filter out teams from the data in tab HOD that didn’t work day 1 through 4. And, finally, I was going to create a tab (Team) to filter the data from the not created tab of just team 12 (who worked days 1-4, and hours 6-17).
Tab TEAM is where I combined the three formulas into one and failed.
So here is what I need: From the data in the DATA tab, I need a formula that will list in a new tab team 12 when they work hours 6-12, and days 1-4. I hope you will see my thinking in tab HOD.
Here is the formula I came up with that did not work:
=IF(COUNTIF(RULES!$A$2:$A$13,DATA!$G2),DATA!A2,IF(COUNTIF(RULES!$H$2:$H$5,DATA!$E2),DATA!A2,IF(DATA!$H2=12,DATA!A2,"")))
=IF(COUNTIF(RULES!$A$2:$A$13,DATA!$G2) ),DATA!A2, == this part filters out the data for those that didn’t work during those hours.
IF(COUNTIF(RULES!$H$2:$H$5,DATA!$E2),DATA!A2 == this part is supposed to filter out the data for those who didn’t work days 1-4.
IF(DATA!$H2=12,DATA!A2,""))) == this part is supposed to filter out the data from all teams except team 12.
I can do each in a separate tab for each filter to get the results, the problem is the data spans about 75,000 lines and 40 columns and takes forever to run (Office 365 Ugh).
If I could attach the spreadsheet I would so you could see it.
Help!
