OK, this sounds like a simple problem, but it is giving me a headache! I have a "what's on when" table for a 9-speed transmission, showing which clutches (labeled A, B, C, D, E, and F) are applied in each gear. For a shift between any two particular gears, user enters the starting gear (FromGear) and the ending gear (ToGear), and I need to populate a table that shows which clutches are applied in each of those two gears.
See the attached file.
Note that there are always three (3), and ONLY three, clutches applied in any gear. For most shifts, two clutches will remain applied (in both the FromGear and ToGear), while the other applied clutch will change. Although there could theoretically be two (or all three) clutches that change state during the shift. The first part of the table (that finds the applied clutches in FromGear, cells D19:D21) is pretty simple and works fine. For the applied clutches in ToGear (cells E19:E21), it is easy to check whether the clutch that was applied in FromGear (column D) is still applied (and if so, display that same clutch letter). But I am having a hard time when the clutch in column D is no longer applied in ToGear ("Help!" in the existing formulas). I have tried to use an array formula, but not having any luck.
Note that, although the "FromGear" clutches will always be listed in ascending order alphabetically, this is not the case for the "ToGear" clutches (which is part of the problem!). For example, in the attached file (using a 7-4 shift), clutches A, E, and F are applied in ToGear (4th). But cell E20 should NOT show clutch E (because it will be shown in row 21 as "still applied"). Cell E20 should show clutch F. So the formula that replaces "Help!" should find the first (or next) clutch that IS applied in ToGear AND was NOT applied in FromGear.
I appreciate any help! Thanks!
Bookmarks