What's mildly irritating you today?

Shawnee123 · 09-25-2009, 12:30 PM

I needed to find duplicate SSNs in an Excel spreadsheet WITHOUT having to delete the dupes (which is the only way I could find to do it within excel itself) because I need to find which ones were erroneously reported twice to the feds. There's an add-on, for which I got a free trial. I am able to just color the rows of the duplicate IDs. I love it.

NOW I need to sort these colored rows out and combine back into another spreadsheet. There's an add-on for THAT, too. No free trial.

Does anyone know if Excel 2007 (can you believe I still have Office 03 at work...sheesh) has these capabilities because if so I'm going to copy the whole damn thing to a thumb drive and work on it at home where I have software from this damn decade.

grumble grumble

glatt · 09-25-2009, 12:36 PM

I don't know, but my firm converted over to Office 2007 a couple months ago and it made everything slow way the hell down. Be glad you have that old software. The newer stuff is like molasses on a cold winter morning.

SteveDallas · 09-25-2009, 01:58 PM

Quote:

Originally Posted by Shawnee123

grumble grumble

If it makes you feel better, I'm still inflicting Office 2000 on my users. (Have you seen my Wikipedia page??)

There are different ways to do this, of course. Quick & Dirty? Here's what I'd do.

Sort by SSN. (We'll assume they're all in the same format.)
You now have all the duplicates next to each other. You can look to see where they are if you only have a dozen or so records. Since I know you have a bajillion times that many, what you want to do is go to a free column over on the right and put in a formula:
Code:
```
=if(a2=a3,"DUPE","")
```
where column a is the column of SSNs, row 2 is the row you're typing the formula on, and row 3 is the next row down. If you auto-fill this forumla down the column, you'll end up with the word "DUPE" on every row for which the SSN matches the SSN on the following row.
Now you can use the Data -> Filter -> Advanced filter to copy only the records that say DUPE in your extra column to another range on the worksheet. (Simply sorting on the column won't work; that will put the SSNs out of order and make the formula you put in useless.)

P.S. I know you're aware of the security risks inherent in carrying the information out on a thumb drive . . .

09-25-2009, 12:30 PM	#1
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	I needed to find duplicate SSNs in an Excel spreadsheet WITHOUT having to delete the dupes (which is the only way I could find to do it within excel itself) because I need to find which ones were erroneously reported twice to the feds. There's an add-on, for which I got a free trial. I am able to just color the rows of the duplicate IDs. I love it. NOW I need to sort these colored rows out and combine back into another spreadsheet. There's an add-on for THAT, too. No free trial. Does anyone know if Excel 2007 (can you believe I still have Office 03 at work...sheesh) has these capabilities because if so I'm going to copy the whole damn thing to a thumb drive and work on it at home where I have software from this damn decade. grumble grumble __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2009, 12:36 PM	#2
glatt ™ Join Date: Jul 2003 Location: Arlington, VA Posts: 27,717	I don't know, but my firm converted over to Office 2007 a couple months ago and it made everything slow way the hell down. Be glad you have that old software. The newer stuff is like molasses on a cold winter morning.

Currently Active Users Viewing This Thread: 2 (0 members and 2 guests)