How do I quickly find and identify duplicate serial numbers within two

[darkrose50]

Are any of you an Excel guru,or know one, and do/does you/he/she tutor? Do you know of the location of any good local school that offers Excel night classes where I can learn these skills?

In Excel I would like to look for and identify duplicate serial numbers either in the same spreadsheet or from two spreadsheets at a click of the mouse so that I do not have to combine the two spreadsheets into one, sort by serial number and check for duplicates manually. This eats up 30-45 minuets of my time daily, and another 45-60 minuets removing said redundancies. This is a step up from taking 6-8 hours and looking at each problem individually, but I lack the Excel knowledge and understanding to take this to the next level and make an 8-hour job a 45-60 minuit job. I also lack the knowledge to then create a macro to close those redundancies in order to make it into a 30-minuit (guess) job that I can have running on another computer while I do other work. Got to love computers, huh?

I bet this problem is most likely often encountered while making mailing address labels when bulk mailing . . . people do not want to pay for postage mailing to the same person twice. The difference is that I want to identify all the duplicate numbers in the spreadsheet and not remove them.

I have identified that Excels “vlookup” may be the very thing I need, but have no clue on how to use it.

The issue where this would be relevant is as follows -

If a First Alarm exists for the same network element (identified by serial number), as a Second Alarm, then the First Alarm is redundant and may be closed with extreme prejudice as an unacceptable waist of time.

I need to find a way to have Excel make a list of First Alarms that may be closed.

1. An Excel spreadsheet is created including a list of First Alarms (including the serial number of the network element in alarm).
2. An Excel spreadsheet is created including a list of Second Alarms (including the serial number of the network element in alarm).
3. Each alarm has a sequenced ticket number.
4. I need Excel to make a list of what serial numbers on the First Alarm spreadsheet exist on the second alarm spreadsheet and what the sequenced ticket number of the First Alarm is.
5. I will then need to figure out how to make a macro to close all those alarms, but that is another problem.

 

[lhite]

I don't have a macro for you, but a procedure to make this task somewhat easier to deal with.

Make sure that you have a field that identifies what table each item comes from (First Alarm or Second Alarm).
combine the two lists together and sort them by Serial Number, then First or Second Alarm.
Next, create a new fields beside the sorted table that says =IF(A2=A1,"SAME","&quot in a field in row 2. This assumes that the Serial Number is in column A, starting with row 2. It compares the current Serial Number with that of the previous row and displays SAME if they are identical, or it remains blank if not. Copy that formula and paste it down the column for the remaining rows. While the whole column is still selected, choose COPY, then go to EDIT and choose PASTE SPECIAL, VALUES. This will convert the formula that you typed in to the text result of the formula so that when you resort the data the duplicate rows still show up that way.
Next, resort the list by the column that has the "SAME" or blank, so that they are all grouped together for easy deletion. Finally, resort the list by Serial Number or whatever order you want. Delete the "SAME" column if desired.
Note that you may have to tweak the formula to look at the row below it if you want to retain the Second Alarm instead of the First Alarm.

As far as learning Excel, I learned by trying things. A class might get you started by showing you some of the capabilities of Excel to get you started.

-Larry

 

[carrr]

Try here...

http://www.cpearson.com/excel/duplicat.htm

This site is an excellent source of pointers for the developing Excel user.

 

[darkrose50]

Thank you very much! I fiddled with it for a few hours and now I can identify duplicates in five to ten minuets (server willing) and get rid of them without me and my co-workers wasting time looking at them. We identified 114 today (“first alarms” and duplicate “second alarms”)! The old way hurt the brain and took four hours and four programs identify all the possible duplicates and then go though about 30 strong possibilities. By that time your head hurt and worked on something else for the rest of the day!