Selecting Duplicates to Keep

[hk1620]

Is there a formula that can be used in a helper column to help me decide which duplicate to keep and which to delete? Below is what I am trying to do

Column A Column B
Company A Status 1
Company A Status 2
Company B Status 1
Company C Status 1
Company C Status 2
Company C Status 3
Company C Status 4
Company D Status 2
Company E Status 1
Company E Status 2

So if there is no duplicate in column A, I want to keep that row, but if there is a duplicate in column A I want to keep status 1 and delete the row with status 2. There is a hierarchy of statuses to keep first. Please note this is not real data. The real data is not in any numerical or alphabetical order otherwise I would sort and remove duplicates.

Example hierarchy:

Status 1
Status 2
Status 3
Status 4

Thank you very much!

[sohaila]

=index(a$2:a$11,small(if(match($a$2:$a$11,$a$2:$a$11,0)=row($a$2:$a$11)-row($a$2)+1,row($a$2:$a$11)-row($a$2)+1),rows($a$2:a2)))

[hk1620]

Thank you for your reply. I was a little bit confused because I put in this formula, but it is only giving me results from column A and I don't think it is taking column 2 into account?

[sohaila]

you drag it to column b

[hk1620]

Ok, thank you! Then how is it determining which value from column B to keep, it looks like it is keeping the first status, which is not what I want to do. I want to be able to specify which one to keep.

[hk1620]

I ended up just assigning a value to each status in another column, and then sorting and removing duplicates.