Sorting, Comparing and Deleting Rows

Please can someone help me solve this.


I have huge data of above 20,000 rows and about 10 columns. Column A have names of items. Column B has functions associated to A. I want to keep only rows of items that have mutiple functions. A sample of my data attached

Data.xlsx

I want to delete all Rows of Diana Grace and Jacob which have just on function (be it Clerk or Manager) and keep all rows of James, Peter and George who have two functions (Manager and Clerk). I will be sorting the data in accordance to column A (names). I hope someone can help me. Thanks in advance.

Thanks for you help sir.
That was ok. But i believe the way i put up my problem it wasn't detailed enough so the solution is not helping me.

Let be give a more detailed and precise overview of my problem.

I have the following columns:

Name Duty Actio Status Message ID Date
hiah A1 FP Amended 48621103 Jan 19 2012 3:22PM
hiah O1 T Created 48621103 Jan 19 2012 3:27PM
mang O1 T Created 35596206 Jan 13 2012 10:29AM
mang S1 R Amended 35731602 Jan 31 2012 9:30AM
beng O1 T Created 35776372 Jan 3 2012 4:10PM
beng S1 R Amended 46336371 Jan 18 2012 9:00AM
hiah O1 T Created 48455604 Jan 12 2012 3:31PM
hiah O1 T Created 48661258 Jan 12 2012 2:26PM
hiah A1 FP Amended 48661258 Jan 12 2012 11:08AM
hiah A1 FP Amended 49088640 Jan 13 2012 10:46AM
hiah A1 FP Amended 49532616 Jan 31 2012 1:56PM
ari S1 R Amended 65607626 Jan 27 2012 3:00PM
kari O1 T Created 65698443 Jan 31 2012 12:15PM
kari S1 R Amended 69561532 Jan 27 2012 11:32AM
kari S1 R Amended 70479495 Jan 30 2012 12:14PM
prah A1 FP Amended 98615038 Jan 31 2012 10:44AM
prah O1 T Amended 98864085 Jan 26 2012 9:16AM
Adi O1 T Created 98908926 Jan 24 2012 9:01AM
Adi S1 R Amended 99211139 Jan 11 2012 11:52AM
Adw O1 T Created 113512273 Jan 26 2012 2:36PM
Adw S1 R Amended 115773085 Jan 11 2012 11:32AM
Adw O1 T Created 115841800 Jan 16 2012 2:43PM
Adw S1 R Amended 122114271 Jan 31 2012 9:15AM
Adw S1 R Amended 122860414 Jan 3 2012 2:40PM
Adw S1 R Amended 124163442 Jan 16 2012 2:51PM
Adw S1 R Amended 128526769 Jan 9 2012 2:15PM
Adw O1 T Created 128526769 Jan 9 2012 2:08PM
Agam O1 T Created 132002806 Jan 18 2012 1:52PM
Agam A1 FP Amended 132002806 Jan 18 2012 1:42PM


In the normal situtation a Name (say Agam or haih) should not have the ability to perform two duties (ie, O1 and A1) on one particular item which is identified by the message ID. But the same name can perform the same duty more than once on one Message ID no mater when. So in order to report on the abnormal situation we want to delete all rows where a Message ID has been worked on by different Names and each name has performed only on duty irrespective of the number times that duty has been performed. Therefor for a row to be on the report it should have another row with the same message ID, same name but different duties. example below:
Name Duty Actio Status Message ID Date
hiah A1 FP Amended 48621103 Jan 19 2012 3:22PM
hiah O1 T Created 48621103 Jan 19 2012 3:27PM
hiah O1 T Created 48661258 Jan 12 2012 2:26PM
hiah A1 FP Amended 48661258 Jan 12 2012 11:08AM
Adw S1 R Amended 128526769 Jan 9 2012 2:15PM
Adw O1 T Created 128526769 Jan 9 2012 2:08PM
Agam O1 T Created 132002806 Jan 18 2012 1:52PM
Agam A1 FP Amended 132002806 Jan 18 2012 1:42PM


I hope this explanations it better. Thanks for your help

Sorry my bad. Here is the attachment

Data.xlsx

Yeh, thats right. In other words I want to keep the rows which column E are the same and column A are the same but column B, column C and column D are different.

Thanks but it did not resolve my problem. Am expecting to get at least two rows for each Message ID (column E) and these rows should have the same names in column A but column B, C and D should be different. an example of the out come is as below


hiah A1 FP Amended 48621103 Jan 19 2012 3:22PM
hiah O1 T Created 48621103 Jan 19 2012 3:27PM


hiah O1 T Created 48661258 Jan 12 2012 2:26PM
hiah A1 FP Amended 48661258 Jan 12 2012 11:08AM


Adw S1 R Amended 128526769 Jan 9 2012 2:15PM
Adw O1 T Created 128526769 Jan 9 2012 2:08PM


Agam O1 T Created 132002806 Jan 18 2012 1:52PM
Agam A1 FP Amended 132002806 Jan 18 2012 1:42PM


I hope you can help me. Thank you very much

Sure I checked and found out that some rows that do not need to appear are left undelete. Example

otor A1 FP Amended 839940893 Jan 19 2012 2:48PM
otor O1 T Amended 840393235 Jan 23 2012 11:55AM
nsah A1 FP Amended 841295795 Jan 9 2012 11:29AM
nsah A1 FP Amended 841685173 Jan 4 2012 10:54AM

and many more. For otor the message IDs are different instead of being the same. whiles for nsah the message ID are also different too. For nsah again the duties are the same instead of being the different.


Could it be such that a search is made using the Message ID and when it is found out there is only one Message ID then that row is deleted. However, if more than one Message ID are found then we search their corresponding names and if any of the names are different we delete that row as well. When the names are the same we check to for different duties. Now if all the duties are the same we delete these rows with this Message ID. However, if the duties are different then we keep them. So in the end when sorted for every message ID there should be at least 2 rows.

Hi Arlette,
sorry it a little complex but i think the deletion should be in stages.
It is ok to sort by the message ID and then Names.

First deletion stage: Then we can delete all rows with which has unique or only one message ID in the entire worksheet.


Still sorted on message ID we go a step further to sort by names with the message IDs.

Second deletion stage: If two or more rows of the same Message ID have different names we have to delete those rows and leave the rows with the same names.


Third deletion Stage: Now with the rows left if for the same message id which now has different names have the same duties the have to be deleted.

I sincerly appologise if my underlining is offensive. Hope you can help me. Thanks

Ok thanks, i be waiting.

Hi Arlette
I hope you haven't forgotten of me.