Multiconditional sort, match and list with exceptions

[kabaa01]

Hello All, I am new to excel so just bear with me.

I have 3 Input columns A B and C as shown below respectively:
Type Request Item Status
Add bbb Completed

The sample data does have numerous rows with 3 different types (Add, Modify and Delete) and 3 different status indications (complete, canceled and denied). The objective is to check for all completed Request Items in the status field, then check for all adds or modifies and make a list; check for all deletes and make a list.

I started by playing around with some formulas as shown below for column D and E:

=IF(AND(C2="Complete",A2="Add"),1, IF(AND(C2="Complete",A2="Delete"), -1, 0))
=IF(AND(C2="Complete",A2="Delete"),2, 0)
And in column F
=IF(D2 = 0, "Ignore This", IF(SUMIF(B:B, B2,D : D ) = 0, "Found One", "Ignore This"))

So to show adds that need deletes in column G
=IF(AND(D : D =1,F:F="Ignore this"),B:B, 0)

To show modifies in column H (NOT sure if this is correct)
=IF(AND(C2="Complete",A2="Modify"),B:B,0)

How would I :

* Ignore all repeated adds, modifies and deletes with the exception of the Request Item “shared drives” for adds.
* Highlight the last add that matches a completed delete according to how it appears on the rows.

Any suggestions would be highly appreciated.

Thanks

[teylyn]

Hello kabaa,

have a look into Pivot Tables for analysing your data. This can easily give you a count of all the possible combinations.

As a starting point, look at http://peltiertech.com/Excel/Pivots/pivotstart.htm