Thread: Clean up a spreadsheet

Hello all,

I have this issue and if anyone could help I would TRULY appreciate it

Here is what I need done:

I have a few spreadsheets with the following columns:

1 - word
2 - address
3 - number

Example spreadsheet rows (A, B, C, D)/columns (1, 2, 3):

1 | 2 | 3
A) word 1 | address X | 23
B) word 1 | address Y | 12
C) word 2 | address Z | 132
D) word 3 | address XY | 68

You will notice that Rows A and B have the same word in Column 1. What I need to do is find a way for me to delete/clear all rows that have the same words and keep just 1 - the one with the lowest number in Column 3. So, the above would turn into:

1 | 2 | 3
A) word 1 | address Y | 12
B) word 2 | address Z | 132
C) word 3 | address XY | 68


It should be simple for someone who knows what they are doing... but I don't. It could also be that a built in function in Excel would do this automatically... but I don't know it lol. So ANY help would be greatly appreciated!

Originally Posted by teylyn

Hello,

sort the spreadsheet by column 3 (by the way, columns are normally referred to with letters, rows with numbers, just like in an Excel sheet)
then sort the spreadsheet by column 1. You will now have the smallest numbers for duplicate entries showing as the first entry
add a new column with this formula starting in row 2 (assuming row 1 has headers)

=IF(COUNTIF($A$1:A2,A2)>1,"duplicate","")

Now sort by that new column and delete all the rows with "duplicate"

In Excel 2007 and later, there's a new command on the ribbon that can remove duplicates with a few clicks.

cheers,

Worked LIKE A CHARM!! Thanks so much for the teylyn!! Much appreciated!