Q&A

# Add difference rows for data in sets

+0
−1

An as yet unanswered question How to compare multiple HLOOKUP cases from @L.Dutch - Reinstate Monica from a very long time ago on Stack Overflow asked whether it was possible to avoid use of a duplicate sheet in the scenario below:

In my sheet I have a formula using HLOOKUP to calculate a number based on the content of a cell. The content can be choosen among "OK","NOK","-".

Deliverable | CASE |Description | Value
Deliverable1 | OK | ******* | 3
Deliverable1 | NOK | ####### | 6
Deliverable1 | - | &&&&&&& | 10
Deliverable2 | OK | ******* | 4
Deliverable2 | NOK | ####### | 7
Deliverable2 | - | &&&&&&& | 9

I want then to calculate, for a given deliverable, the difference between the applied case and the case when the selection is put to "OK". I.e. if I have a -, I want to get 7.

To achieve this I have created a duplicate sheet where I force the content of the cell to be OK, and then I calculate the difference. The problem with this approach is that when using filtering or sorting the calculation is messed up.

Is this possible without a duplicate sheet?

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

+0
−0

Assuming:

1. only the difference between the `Value` for `CASE` of `-` and that for `CASE` of `OK` is required for each `Deliverable`
2. that `Deliverable` is in `A1`
3. that each `Deliverable` always has the same three `CASE`
4. That Column A is sorted A-Z and, within ColumnA, ColumnB is sorted Z-A

then with the Subtotal feature a duplicate sheet should not be required.

1. Select ColumnsA:D, DATA > Outline – Subtotal, OK then Sort & Filter – Filter.

2. Filter ColumnA with Text Filter…, Contains…, `tot`, OK.

3. In D5 enter and copy down to suit:

`=D4-D2`

4. Clear filter and select Group2 (click on the small `2` at the top left).

5. Select all visible and Copy/Paste Special…, Values over the top.

Optionally: Ungroup rows twice; replace `Total` in ColumnA with `Diff -/OK`; delete Grand Total row.

Why does this post require moderator attention?
You might want to add some details to your flag.