Jedi Master
Board Regular
- Joined
- Jun 10, 2024
- Messages
- 70
- Office Version
- 365
- Platform
- Windows
I have pasted 5 mini sheets with data. In the 6th sheet, I have a formula that combines the data from the previous 5, removing blanks. Column "L" then filters column A for only unique values from the newly created array and sums the corresponding column D and column J values in columns M and N. I need a little help with on sheet 6, combining what ive done so far into a single array with the unique values from column M (or A) keeping all values in columns (B,C,E,F,G,H,I, and K), and replacing values in D and J with values in M and N (M for D, and N for J). Unless there is a better way to do this?
I need to basically create a filtered array, and then filter it again, adding the sum total values in both columns d and J to a unique original in column A. Im sorry if this is confusing, but that is why I am reaching out for help. Any assistance would be greatly appreciated!
I need to basically create a filtered array, and then filter it again, adding the sum total values in both columns d and J to a unique original in column A. Im sorry if this is confusing, but that is why I am reaching out for help. Any assistance would be greatly appreciated!
| ChatGPT Test.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 48 | MACHINES | ||||||||||||
| 49 | Item | Category | Use Daily or Hourly | Days or Hours Used | Rate Adj % | Daily Rate | Sub-Total | Fuel GPH | In Use Hours | Total Est Fuel | |||
| 50 | 4x4 Crew Truck | Pickup Truck | Daily | 3 | ← Enter Days | 115% | $115.00 | $345.00 | 2.6 | 6 | 15.6 | ||
| 51 | 4x4 Stakebed Truck | Flatbed Truck | Daily | 3 | ← Enter Days | 120% | $156.00 | $468.00 | 2 | 6 | 12 | ||
| 52 | 100% | 0 | |||||||||||
| 53 | 100% | 0 | |||||||||||
| 54 | Kubota KX080 | EXCAVATOR | Daily | 3 | ← Enter Days | 100% | $656.25 | $1,968.75 | 3 | 24 | 72 | ||
| 55 | Kubota SVL95 | Skid Loader | Daily | 3 | ← Enter Days | 100% | $487.50 | $1,462.50 | 3 | 24 | 72 | ||
| 56 | Compactor, Soil - Vibratory Double Drum Roller 40-49" | Compactor, Soil - Vibratory Double Drum Roller 40-49" | Daily | 3 | ← Enter Days | 100% | $278.00 | $834.00 | 1.5 | 12 | 18 | ||
| 57 | 100% | 0 | |||||||||||
| 58 | 100% | 0 | |||||||||||
| 59 | Attachment - Broom | Attachment - Broom | Daily | 3 | ← Enter Days | 100% | $84.30 | $252.90 | 0 | 0 | 0 | ||
| 60 | Morooka L | Tracked carrier | Daily | 3 | ← Enter Days | 100% | $890.00 | $2,670.00 | 3 | 18 | 54 | ||
| 61 | PORTA-JOHN (Pair) | Portable Toilets (PAIR) | Daily | 3 | ← Enter Days | 100% | $35.00 | $105.00 | 0 | 0 | 0 | ||
| 62 | 100% | ||||||||||||
| 63 | 100% | ||||||||||||
| 64 | 100% | ||||||||||||
| 65 | 100% | ||||||||||||
| 66 | 100% | ||||||||||||
| 67 | 100% | ||||||||||||
| 68 | 100% | ||||||||||||
| 69 | 100% | ||||||||||||
| 70 | 100% | ||||||||||||
| 71 | 100% | ||||||||||||
| 72 | 100% | ||||||||||||
| 73 | 100% | ||||||||||||
Tab1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B50:B73 | B50 | =IF(ISBLANK(A50),"",INDEX('[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!D:D,MATCH(A50,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!C:C,0))) |
| C50:C72 | C50 | =IF(ISBLANK($A50),"",IF($B$20="TC Energy",VLOOKUP($A50,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!$C:$G,5,0),"DWD Std Daily")) |
| C73 | C73 | =IF(ISBLANK($A73),"",IF($B$20="TC Energy",VLOOKUP($A73,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!$A:$G,7,0),"DWD Std Daily")) |
| G50:G73 | G50 | =IF(ISBLANK(A50),"",F50*VLOOKUP(A50,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!C:L,'Tab1'!$U$49,0)) |
| H50:H73 | H50 | =IF(ISBLANK($A50),"",F50*($D50*VLOOKUP($A50,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!C:L,'Tab1'!$U$49,0))) |
| I50:I73 | I50 | =IF(ISBLANK(A50),"",VLOOKUP(A50,'[Ambassador Christian School NC Football Field.xlsm]MASTER_EQUIP'!C:T,18,0)) |
| E50:E73 | E50 | =IF(ISBLANK(A50),"",IF(C50="dwd Std Daily","← Enter Days",IF(C50="Daily","← Enter Days",IF(C50="Hourly","← Enter Hours","Fix Equip List")))) |
| K50:K73 | K50 | =IF(ISBLANK(A50),"",(I50*J50)) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E50:E73 | Cell Value | contains "Hours" | text | NO |
| E50:E73 | Cell Value | contains "Days" | text | NO |
| E50:E73 | Cell Value | contains "Fix" | text | NO |
| H50:H73 | Cell Value | =0 | text | NO |
