Delete rows based on values in another cell

[jimboc]

Hi Everyone,

I'm working on some data manipulation in Excel but I've gotten stuck. Basically, I'm taking a list of file names and parsing the file names so I have a Part Number and a Revision letter in each column. I wrote a little macro to do that and it's working great.

However, now I need to delete the duplicate part numbers and only keep the latest revision. A sample of the data would be:

Part Number / Revision
12345 A
12345 B
22222 F
22222 L
22222 B
99999 DC
99999 DD

I only want to keep the row that contains the latest revision but I can't figure out how to do it. The rows won't necessarily be in any order but I can sort them by part number and revision if that would help. I've searched all over and have found lots of ways to delete rows where a cell contains a certain value but in this case I need to determine the latest first.

The results I'd like would be
12345 B
22222 L
99999 DD

Can anyone help? Thanks in advance!!

-j

[rylo]

Hi

How about

Please Login or Register  to view this content.

Data is in columns A and B, with headers in row1, and no data in column C


HTH

rylo

[jimboc]

Thanks!

That was a big help. There's one more thing that would be helpful but I haven't been able to figure it out.

My spreadsheet has multiple columns and the data I want to sort by isn't in column A. I tried changing the A and B in the code but that didn't work. I guess I thought it would be easy!

Here's a link to my spreadsheet. http://www.afifthtaste.com/TESTING2a.xls

I start with a file name in column A and then there's a path in column B. In column C I strip the file extension and then in column D and F I parse the file name into two fields: part number and revision.

I want to use the data in column D and F to sort and find duplicate part numbers. The code you gave me works if I paste the data from D and F into columns A and B. Also, E is empty now but will have some data in it eventually.

Thanks in advance for any ideas!

[rylo]

Hi

See if this gets you there.

Please Login or Register  to view this content.

rylo

[jimboc]

Thanks!

I had just figured out that I needed to use "4" so the proper column would be used but I wasn't sure about the Range change. Looks like it's working.

Thanks again for the help!

-j