Hi all,
Below is the part of the dataset I have.
As you can see, there are 4 columns: Model, Sequence, Applied Date, and Expected Cost.
Each model has different sequence numbers and each sequence numbers have different expected cost.
What I want to do is to extract only one corresponding sequence in the "Sequence" column for max value in the "Expected Cost" for each model using a formula.
For example,
Model: WT1701CV.ASSEEUS; Sequence: 2 (This model has 3 sequence number. I need to choose one sequence number that has highest expected cost. Sequence 2 has been chosen because sequence number 2 has largest expected cost with 116 among other sequence numbers, 2 and 3 for model, WT1701CV.ASSEEUS).
Model: WM3170CW.ABWEPUS; Sequence: 3
Model: WM4270HWA.ABWEPUS; Sequence: 2
Could you please tell me a formula that could provide this information?
Your help will be greatly appreciated.
<tbody>
</tbody>
Below is the part of the dataset I have.
As you can see, there are 4 columns: Model, Sequence, Applied Date, and Expected Cost.
Each model has different sequence numbers and each sequence numbers have different expected cost.
What I want to do is to extract only one corresponding sequence in the "Sequence" column for max value in the "Expected Cost" for each model using a formula.
For example,
Model: WT1701CV.ASSEEUS; Sequence: 2 (This model has 3 sequence number. I need to choose one sequence number that has highest expected cost. Sequence 2 has been chosen because sequence number 2 has largest expected cost with 116 among other sequence numbers, 2 and 3 for model, WT1701CV.ASSEEUS).
Model: WM3170CW.ABWEPUS; Sequence: 3
Model: WM4270HWA.ABWEPUS; Sequence: 2
Could you please tell me a formula that could provide this information?
Your help will be greatly appreciated.
1 | A | B | C | D |
2 | Model | Sequence | Apply Date | Expected Cost |
3 | WT1701CV.ASSEEUS | 2 | 201506 | 116 |
4 | 3 | 201506 | 38 | |
5 | 4 | 201506 | 45 | |
6 | WM3170CW.ABWEPUS | 2 | 201506 | 17 |
7 | 3 | 201506 | 568 | |
8 | WM4270HWA.ABWEPUS | 2 | 201506 | 276 |
9 | 3 | 201506 | 63 | |
10 | DLGX8001W.ABWEEUS | 2 | 201506 | 194 |
11 | 3 | 201506 | 76 |
<tbody>
</tbody>