Compare "cells" from 2 fields and delete duplicate

[shad0w4life]

this might sound all fine and dandy but...

ColumnA ColumnB

ABC 1A
ABC 1B
ABC 1C
DEF 1A
GHI 3
JKL 4C
ABC 2A
GHI 4

Now in this column what I need the results to show up as are
ABC 1C
ABC 2A
DEF 1A
GHI 3
GHI 4
JKL 4C



Basically I need to keep the "highest" ColumnB being that on a scale A is the lowest and Z would be the highest in the alpha scale so 1A < 1B AND Keep each seperate # eg. i the table I have the highest ColumnB for the 1's and the highest for the 2's


I ran this macro in excel but is access i'm completely new to VBA in it. Is it anything similar to Excel if so I'll post the code.

 

[Norie]

I don't think you actually need a macro for this.

It could possibly be done using a Totals query.

But I'm not 100% sure about the logic behinf what you want to do.

 

[shad0w4life]

Re: Compare "cells" from 2 fields and delete dupli

Ok I'll try and explain it best I can, work has mad security so i can't use that excel builder program etc.

I have a DB where I need to compare the "name" and "Revision" in each row and only extract 1 unique "name" record per each "revision" and then show a table with the results. eg. on my table above 1a 1b 1c are all the same revision except C is the newest so I would want to show that record on the new table and not the b and a one's.

If that made any sense, basically trying to find the Maximum Revision for each "name"s unique revision series(1a 1b 1c is one series, 2a 2b 2c is a different series)

So

ABC 1A
ABC 1B
ABC 1C
ABC 2A
ABC 2B
DEF 4
DEF 5


Would end up looking like
ABC 1C
ABC 2B
DEF 4
DEF 5

 

[shad0w4life]

Re: Compare "cells" from 2 fields and delete dupli

here's the solution if anyone was curious

"
Posted on 01/24/05 06:30 PM
Posted by kikovp - Veteran
Posts: 304 - Loc: Lisboa, Portugal

Try creating a query like:

Select ColumnA, Mid$(ColumnB,1,1) as ColBLetter, Max( ColumnB ) As MaxColB
From MyTable
Group by ColumnA, Mid$(ColumnB,1,1);

HTH
"