Compare and delete in excel

[Guest]

Hello All ,

here is some data i have in Excel in just one row

15023199 C
15023199 D
15023200 A
15023200 D
15023199 E
15023199 B
........
....
....
The Number Here is the sequence number and the Alphabet here is the
confidence level.
I need to compare data of each row with other and delete the row with same
sequence number and lowerconfidence level (A --high confidence----E-- high
confidence)

so inthe example above
15023199 should get B and 15023200 Should have an A.

thanks

 

[Pete_UK]

If you can sort this data by sequence number then by confidence level,
you would have:

15023199 B
15023199 C
15023199 D
15023199 E
15023200 A
15023200 D

Then the value you want to retain will always be the first in any
block of numbers - this will make your coding easier to implement.

Hope this helps.

Pete

 

[Guest]

i am week in coding stuff in excel, actually preety new ,

can you help me to build some macros or some VB code .

thanks for your reply

 

[Pete_UK]

You don't need to do it by macro, but it is easy enough to turn on the
macro recorder to record the following actions and then play them back
in the future if you need to repeat them.

Ensure you have headings in row 1 - eg "Serial" in A1 and "Level" in
B1. Highlight all the data, including the headings and Data | Sort,
click Header Row and sort by Serial then by Level and click OK. Enter
a heading in C1 - "Check" - and this formula in C2:

=IF(A2=A1,"Remove","Keep")

Copy the formula down column C for as many entries as you have in A
and B. Highlight column C by clicking on the column identifier then
Data | Filter | Autofilter. Using the pull-down arrow in C1, select
"Remove". Highlight all the visible rows (not the headings) then Edit
| Delete Row. Use the pull-down in C1 to select All. Highlight column
C then Edit | Delete, and you will end up with the unique values you
require.

Hope this helps.

Pete

 

[Guest]

Thanks pete,

It works fine..but kind on manual process, will try to put it in a macro,

thanks a lot

 

[Pete_UK]

Turn the macro recorder on while you do it and you will have your
macro. You may need to edit it, for example to suit the number of data
items you have each time you run.

Good luck, and thanks for feeding back.

Pete