Compare "cells" from 2 fields and delete duplicate

shad0w4life

New Member
Joined
Nov 4, 2004
Messages
48
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 4, 2004
Messages
48
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

New Member
Joined
Nov 4, 2004
Messages
48
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
"
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top