Mark all as read

Add difference rows for data in sets


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?


1 answer



  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:


  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.


Sign up to answer this question »