misc data filter

  • Thread starter Thread starter Soe
  • Start date Start date
S

Soe

Item Activity Time
ABC IN 100
DEF OUT 110
ABC OUT 120
DEF IN 130

IN & OUT are pairs, if one item is IN, it must be OUT.
I am looking for a way to find items which are IN but no OUT. In this
example data, "DEF" was OUT and IN again, then I need to locate that DEF.
For a case of "ABC", it is already out, then I am not interested.
I have tried this with pivot table but in vain.
I would appreciate much if somebody can find a solution for this.
thankyou.
 
Soe,

With your table in A:C starting in row 1, enter this array formula in
cell D2 using Ctrl-Shift-Enter:

=IF(B2="In",IF(SUM((A3:A10=A2)*(B3:B10="OUT")*1)=0,"Flag",""),"")

(Change the row 10 references to the actual row, as needed.)

Then copy cell D2 down as far as your data extends, and then you can
filter your table on column D, choosing "Flag" to show which items are
IN and not Out.

HTH,
Bernie
 
Back
Top