Need a delte rows macro (based on a criteria)

[mortgageman]

Hi all -

I have a spreadsheet that looks similar to this

Course Name Other1 Other2 Id
Math a b 123
Math II a b 123
Math 1 a b 456

I want to delete any rows that do not repeat the ID

Any ideas?

Gene Klein

 

[texasalynn]

could you give an example of one that would be selected to delete?

 

[mortgageman]

could you give an example of one that would be selected to delete?

Sure - the last row since the 456 doesn't repeat (as opposed to the 123 that does.

Gene Klein

 

[xenou]

You can put a helper column in:

=CountIF($E$2:$E$65536,E2)

Where E2 is the first ID value. Then filter on Count of 1. This is probably one of many other ways. You could also sort by ID and use an IF formula to mark ID's that are the same as the ID above it. Office 2007 introduced a Find Duplicates tool though I personally haven't used it yet (dunno why not). Finally, you can drop it into Access for a quick job with SQL.

 

[mortgageman]

You can put a helper column in:

=CountIF($E$2:$E$65536,E2)

Where E2 is the first ID value. Then filter on Count of 1. This is probably one of many other ways. You could also sort by ID and use an IF formula to mark ID's that are the same as the ID above it. Office 2007 introduced a Find Duplicates tool though I personally haven't used it yet (dunno why not). Finally, you can drop it into Access for a quick job with SQL.

I will try these when I get to work tommorow. Thank you

Gene Klein