up vote 2 down vote favorite
share [g+] share [fb]

We have a list of computers in an excel table, a few thousand. Most are named in the format of <3digit office code> - . For example, NYC-1234567. Sometimes, when machines get re-imaged, the techs will add a few digits to the end, since they don't want to delete the machine from the domain (or don't have the rights), so for example, NYC-1234567b or NYC-1234567-2

We also have some machines that might have been moved to another office, for example, if moved to LA, it might become LAX-1234567.

I need to "get rid" of duplicates in our spreadsheet to clean up licensing. I have played a bit with conditional formatting. I see I can set a formula for cell A2 conditional formatting of =COUNTIF(A:A,A2)>1 which will trigger on any duplicates. But how would I compare just the serial numbers, characters 4-11? I am at a bit of a loss. I'm not much of an Excel person.

link|improve this question
feedback

1 Answer

up vote 2 down vote accepted

Create a new column and use this formula:

=LEFT(RIGHT(A1; (LEN(A1) - FIND("-"; A1))); 7)

After that just use the conditional formating on that column to find duplicates.

link|improve this answer
1  
That is awesome, thank you. I did have one problem, excel 2007 didn't like the semicolons, and I had to change it to comma's if someone else is going to try this. – Brian Nov 16 '10 at 17:52
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.