Ol Reliable
New Member
- Joined
- Dec 18, 2024
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Good evening, I'm at my wits end when trying to tackle the issue I'm having so lets start at the beginning with a little breakdown of my problem. Forgive me if I overshare.
TLDR:
I have 40 unique vehicle ID's in Column A formatted alongside a calendar with date headings. I am trying to compare Today to Yesterday for each vehicle that is in my possession to see if the number of maintenance actions increased or decreased. I need both of those totals as separate cells for end-of-month filing. It's not as simple as adding all my columns up and comparing to the previous day as my vehicles are constantly coming and leaving from the shop. I am only concerned about vehicles that are in my possession but if a vehicle leaves the shop on say, a Monday, and returns on the following Monday, I need to keep checking through all days that are N/A or BLANK until I find that number and do my comparison. Is this a lost cause? Am I doomed to doing mental gymnastics at the end of my very long days?
BACKGROUND:
- My agency blocks VBA so I cannot use visual basic to solve my problem so I'm restricted to formula based solutions.
- I have a master sheet that will be my front end display and I have additional sheets that I'm going to be hiding once the operations are working.
- My master sheet is a Calendar that I aptly named as such for clarity but it's not formatted as a typical calendar. It starts on the 27th of November in the C column and then will continue incrementally increasing every column ad infinitum.
- In the A column I have a list of 40 unique vehicle ID's I'm tracking for number of number of maintenance actions. This information should be irrelevant in this problem but I posted this information for clarity's sake.
- For each unique vehicle ID, I need to compare Today to Yesterday to see whether the amount of maintenance actions increased or decreased and total each respective to each other. For some instances where the vehicle wasn't in my possession yesterday, I need to keep looking back through that history until I find its last entry and compare that number to Today.
- I don't absolutely have to have a record of maintenance actions saved to this workbook but it is nice to have a one-stop-shop that I can review back on old data but I do need to have a record of maintenance actions that either increased and decreased for records keeping purposes. I based and built this current workbook off of memory from the old workbook that I had stored in a Teams folder that was deleted and unrecoverable so I am rebuilding this from scratch with additional features. I had to do these calculations by hand and it is very time consuming. I'm trying to work smarter than harder. Also, that old file didn't have these nice features.
- My 3 sheets are named as follows: Calendar, Calendar_Add, and Calendar_Sub. Both the _Add and _Sub would be hidden and are only placeholders for formulas. There are additional sheets in this workbook that do other things, mainly to help me log the daily maintenance actions.
- Below is an example of how the front end spread sheet is displayed. Column A is the Vehicle ID, that number will not change. I have 40 Vehicles I am tracking maintenance actions. N/A means that the vehicle is not in the shop so therefore, I don't have data on the vehicle until it returns to the shop.
- Weekends are blank because I don't want to come into the shop to collect that data, who likes working weekends? Not this guy.
- Lastly, in my project, the vehicle ID's start on cell A10. A1:A8 are reserved for additional information that I may need to add. A9 starts the Headers.
- If the above rows are labeled A through J (etc...), I need to compare H2 to G2 for example and see if it increased or decreased and document how much. I need to do that to all cells that have maintenance data in it.
CURRENT DATA:
To make my current formula as "simple" as possible, I created two soon-to-be hidden sheets and I'm currently working on the Calendar_Add sheet which should take the current cell and compare it to the cell to its immediate left. It checks to see if it is greater, less or equal to. If it's equal to, it writes a 0. If it's less than, it will subtract THIS cell from THAT cell. If it's greater then, it writes a 0. I wrote the following which works for Tuesday through Friday, but Monday doesn't work properly.
Let me break down my mental illness that is that code, bear in mind that it would be one continuous string of code but to save what little sanity I have left, I broke it apart for this example.
Line 1: If THIS cell(H10) is N/A, make it 0
Line 2: If THIS cell(H10) is GREATER than THAT cell(G10) then subtract G10 from H10
Line 3: If THIS cell is EQUAL to THAT cell, make it 0
Line 4: This is where I try to check for a weekend which would be all blank cells. If the left two cells are BLANK AND THIS cell(H10) is GREATER than the 3rd cell on the left (which should have a number in it) then do the math.
Line 5: Weekend check, compare THIS cell to what would be Friday, if they are EQUAL, make it 0
Line 6: The same as Line 5 but if they are LESS than, make it 0
Line 7: Weekend check and a Friday holiday check (N/A), follows basic math
Line 8: Weekend check and a Friday holiday check EQUAL, make it 0
Line 9: Weekend check and a Friday holiday check LESS than, make it 0
Line 10: Weekend check and a Friday & Thursday holiday check (N/A), follow basic math
Line 11: Weekend check and a Friday & Thursday holiday check, EQUAL, make it 0
Line 12: Weekend check and a Friday & Thursday holiday check, LESS than, make it 0, If FALSE make it 0
H10 was just where I was going to drop the formula then drag the little green box to automatically adjust the cell references so that cell reference shouldn't matter, at least I don't think so. I may be wrong.
With all that said, using the above formula on the example spreadsheet:
With this data, I can add up each column and pull that info over to the main Calendar sheet to show me how many added maintenance actions were to each vehicle. It's not so simple as just adding up all the cells in one column then comparing that to the sum of the column in front of it because it doesn't tell me exactly how many jobs were added due to the possibility that another vehicle's maintenance actions went down. My problem is that Monday isn't pooling through the weekend correctly nor can I determine if the check for N/A is working. The 27th of November breaks because that was the first day I started collecting this data so it has nothing in front of it to check and I don't want it to pull the vehicle ID into the formula and collect wrong data. I'm not married to using nested IF statements, if there's another less memory intensive way of solving this problem, I'm all ears. I thought maybe an INDEX MATCH function might work but I have no idea where to start, that or using an ArrayFormula or a Pivot Table for Pete's sake... I'm willing to try anything. If I don't have to use the extra sheets then sure. I'm at a loss.
Some potential pitfalls that could potentially happen during the daily collection of this data:
- What happens if all cells in a row are N/A?
- What happens when THAT cell is N/A when compared to THIS cell? Repeat checking left until I get a number to compare to.
Lastly, I have a conditional format for weekend dates: =IF(WEEKDAY(B9)>2, FALSE, TRUE) .... B9 is the first cell where the calendar starts.
It may not matter but I was going to total each column in this example to Row 6, and when I finish my Subtraction sheet, its data would get added to Row 7.
Well, that's that. Hopefully there's a much easier way of pulling and organizing this data that my simple mind cannot fathom. Please help me Excel Gurus.
-Ol' Reliable
TLDR:
I have 40 unique vehicle ID's in Column A formatted alongside a calendar with date headings. I am trying to compare Today to Yesterday for each vehicle that is in my possession to see if the number of maintenance actions increased or decreased. I need both of those totals as separate cells for end-of-month filing. It's not as simple as adding all my columns up and comparing to the previous day as my vehicles are constantly coming and leaving from the shop. I am only concerned about vehicles that are in my possession but if a vehicle leaves the shop on say, a Monday, and returns on the following Monday, I need to keep checking through all days that are N/A or BLANK until I find that number and do my comparison. Is this a lost cause? Am I doomed to doing mental gymnastics at the end of my very long days?
BACKGROUND:
- My agency blocks VBA so I cannot use visual basic to solve my problem so I'm restricted to formula based solutions.
- I have a master sheet that will be my front end display and I have additional sheets that I'm going to be hiding once the operations are working.
- My master sheet is a Calendar that I aptly named as such for clarity but it's not formatted as a typical calendar. It starts on the 27th of November in the C column and then will continue incrementally increasing every column ad infinitum.
- In the A column I have a list of 40 unique vehicle ID's I'm tracking for number of number of maintenance actions. This information should be irrelevant in this problem but I posted this information for clarity's sake.
- For each unique vehicle ID, I need to compare Today to Yesterday to see whether the amount of maintenance actions increased or decreased and total each respective to each other. For some instances where the vehicle wasn't in my possession yesterday, I need to keep looking back through that history until I find its last entry and compare that number to Today.
- I don't absolutely have to have a record of maintenance actions saved to this workbook but it is nice to have a one-stop-shop that I can review back on old data but I do need to have a record of maintenance actions that either increased and decreased for records keeping purposes. I based and built this current workbook off of memory from the old workbook that I had stored in a Teams folder that was deleted and unrecoverable so I am rebuilding this from scratch with additional features. I had to do these calculations by hand and it is very time consuming. I'm trying to work smarter than harder. Also, that old file didn't have these nice features.
- My 3 sheets are named as follows: Calendar, Calendar_Add, and Calendar_Sub. Both the _Add and _Sub would be hidden and are only placeholders for formulas. There are additional sheets in this workbook that do other things, mainly to help me log the daily maintenance actions.
- Below is an example of how the front end spread sheet is displayed. Column A is the Vehicle ID, that number will not change. I have 40 Vehicles I am tracking maintenance actions. N/A means that the vehicle is not in the shop so therefore, I don't have data on the vehicle until it returns to the shop.
- Weekends are blank because I don't want to come into the shop to collect that data, who likes working weekends? Not this guy.
- Lastly, in my project, the vehicle ID's start on cell A10. A1:A8 are reserved for additional information that I may need to add. A9 starts the Headers.
| Veh ID | 27-Nov-24 | 28-Nov-24 | 29-Nov-24 | 30-Nov-24 | 1-Dec-24 | 2-Dec-24 | 3-Dec-24 | 4-Dec-24 | 5-Dec-24 |
|---|---|---|---|---|---|---|---|---|---|
| 14001456 | 38 | N/A | N/A | 39 | 38 | 39 | 42 | ||
| 15001234 | 20 | N/A | N/A | 19 | 20 | 18 | N/A | ||
| 15104567 | 28 | N/A | N/A | 28 | N/A | N/A | 29 | ||
| 16202123 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | ||
| 16303456 | 30 | N/A | N/A | 30 | 29 | 30 | 31 | ||
| 20101234 | N/A | N/A | N/A | N/A | N/A | N/A | N/A |
- If the above rows are labeled A through J (etc...), I need to compare H2 to G2 for example and see if it increased or decreased and document how much. I need to do that to all cells that have maintenance data in it.
CURRENT DATA:
To make my current formula as "simple" as possible, I created two soon-to-be hidden sheets and I'm currently working on the Calendar_Add sheet which should take the current cell and compare it to the cell to its immediate left. It checks to see if it is greater, less or equal to. If it's equal to, it writes a 0. If it's less than, it will subtract THIS cell from THAT cell. If it's greater then, it writes a 0. I wrote the following which works for Tuesday through Friday, but Monday doesn't work properly.
Excel Formula:
=IF(Calendar!H10="N/A", "0",
IF(Calendar!H10>Calendar!G10, (Calendar!H10-Calendar!G10)),
IF(Calendar!H10=Calendar!G10, "0")
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!H10>Calendar!E10), (Calendar!H10-Calendar!E10),
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!H10=Calendar!E10), "0",
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!H10<Calendar!E10), "0",
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!H10>Calendar!D10), (Calendar!H10-Calendar!D10),
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!H10=Calendar!D10), "0",
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!H10<Calendar!D10), "0",
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!D10="N/A", Calendar!H10>Calendar!C10), Calendar!H10-Calendar!C10),
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!D10="N/A", Calendar!H10=Calendar!C10), "0",
IF(AND(Calendar!G10="", Calendar!F10="", Calendar!E10="N/A", Calendar!D10="N/A", Calendar!H10<Calendar!C10), "0", "0")))))))))Let me break down my mental illness that is that code, bear in mind that it would be one continuous string of code but to save what little sanity I have left, I broke it apart for this example.
Line 1: If THIS cell(H10) is N/A, make it 0
Line 2: If THIS cell(H10) is GREATER than THAT cell(G10) then subtract G10 from H10
Line 3: If THIS cell is EQUAL to THAT cell, make it 0
Line 4: This is where I try to check for a weekend which would be all blank cells. If the left two cells are BLANK AND THIS cell(H10) is GREATER than the 3rd cell on the left (which should have a number in it) then do the math.
Line 5: Weekend check, compare THIS cell to what would be Friday, if they are EQUAL, make it 0
Line 6: The same as Line 5 but if they are LESS than, make it 0
Line 7: Weekend check and a Friday holiday check (N/A), follows basic math
Line 8: Weekend check and a Friday holiday check EQUAL, make it 0
Line 9: Weekend check and a Friday holiday check LESS than, make it 0
Line 10: Weekend check and a Friday & Thursday holiday check (N/A), follow basic math
Line 11: Weekend check and a Friday & Thursday holiday check, EQUAL, make it 0
Line 12: Weekend check and a Friday & Thursday holiday check, LESS than, make it 0, If FALSE make it 0
H10 was just where I was going to drop the formula then drag the little green box to automatically adjust the cell references so that cell reference shouldn't matter, at least I don't think so. I may be wrong.
With all that said, using the above formula on the example spreadsheet:
| Veh ID | 27-Nov-24 | 28-Nov-24 | 29-Nov-24 | 30-Nov-24 | 1-Dec-24 | 2-Dec-24 | 3-Dec-24 | 4-Dec-24 | 5-Dec-24 |
|---|---|---|---|---|---|---|---|---|---|
| 14001456 | #REF | 0 | 0 | 0 | 0 | 39 | 0 | 1 | 3 |
| 15001234 | #REF | 0 | 0 | 0 | 0 | 19 | 1 | 0 | 0 |
| 15104567 | #REF | 0 | 0 | 0 | 0 | 28 | 0 | 0 | 0 |
| 16202123 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 16303456 | #REF | 0 | 0 | 0 | 0 | 30 | 0 | 1 | 1 |
| 20101234 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
With this data, I can add up each column and pull that info over to the main Calendar sheet to show me how many added maintenance actions were to each vehicle. It's not so simple as just adding up all the cells in one column then comparing that to the sum of the column in front of it because it doesn't tell me exactly how many jobs were added due to the possibility that another vehicle's maintenance actions went down. My problem is that Monday isn't pooling through the weekend correctly nor can I determine if the check for N/A is working. The 27th of November breaks because that was the first day I started collecting this data so it has nothing in front of it to check and I don't want it to pull the vehicle ID into the formula and collect wrong data. I'm not married to using nested IF statements, if there's another less memory intensive way of solving this problem, I'm all ears. I thought maybe an INDEX MATCH function might work but I have no idea where to start, that or using an ArrayFormula or a Pivot Table for Pete's sake... I'm willing to try anything. If I don't have to use the extra sheets then sure. I'm at a loss.
Some potential pitfalls that could potentially happen during the daily collection of this data:
- What happens if all cells in a row are N/A?
- What happens when THAT cell is N/A when compared to THIS cell? Repeat checking left until I get a number to compare to.
Lastly, I have a conditional format for weekend dates: =IF(WEEKDAY(B9)>2, FALSE, TRUE) .... B9 is the first cell where the calendar starts.
It may not matter but I was going to total each column in this example to Row 6, and when I finish my Subtraction sheet, its data would get added to Row 7.
Well, that's that. Hopefully there's a much easier way of pulling and organizing this data that my simple mind cannot fathom. Please help me Excel Gurus.
-Ol' Reliable
