kabaa01
04-14-2010, 01:17 PM
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, cancelled 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 do I:
Ignore all repeated adds, modifies and deletes with the exception of the Request Item “shared drives”.
Highlight the last add that matches a completed delete.
If request item is part of a “not included” Request Item list ignore it.
List all completed modifies Any suggestions would be highly appreciated.
Thanks
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, cancelled 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 do I:
Ignore all repeated adds, modifies and deletes with the exception of the Request Item “shared drives”.
Highlight the last add that matches a completed delete.
If request item is part of a “not included” Request Item list ignore it.
List all completed modifies Any suggestions would be highly appreciated.
Thanks