Remove all duplicates and return highest value in new row

[Nikkynock]

Hi

I am looking to locate all duplicates of a descriptor in column a, remove,
and create a new row with just the descriptor and the highest value in column
b.
For example

col a col b
generice code 1 000123456
generice code 1 000123678
generice code 1 000123910

Returns:
generice code 1 000123910

....and removes all other lines

many thanks

 

[Gary''s Student]

Try this small macro:

Sub nikky()
Dim v1 As String, v2 As Long
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
v1 = Cells(i, 1).Value
v2 = Cells(i, 2).Value
For j = n To 1 Step -1
If Cells(j, 1).Value = v1 And Cells(j, 2).Value > v2 Then
Cells(i, 1).EntireRow.Delete
GoTo nextone
End If
Next
nextone:
Next
End Sub

So if we start with:

cat 1
mouse 1
dog 66
mouse 4
dog 5
mouse 2
cat 34
dog 3


the macro will produce:

dog 66
mouse 4
cat 34

 

[nikkynock]

hi

cannot run as vba debug highlights v2 = Cells(i, 2).Value as the issue.
Being completely illiterate in vba, i'm not sure what how to fix

thanks

 

[Gary''s Student]

If the v2 line fails, that probably means the the values in column B are not
real numbers, they may be Text instead.

We either need to insure that they are numbers or find a way to make them
numbers.

 

[nikkynock]

hi
think you are right - the data is barcode data from a sales info provider.
the data looks like this when first imported into excel: 5.01111E+12. I
change cells to 'number' with 0 decimal points to show the actual number (in
this example .01111E+12 = 5011111805106), but i guess this is still not seen
as a number in the cell? anything i can do to convert - i tried text to
columns but this has not helped?

 

[Gary''s Student]

5011111805106 is a valid number. Are any values more than 15 digits??

 

[nikkynock]

the max number of digits in the string is 13 - any ideas?