Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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