Remove all duplicates and return highest value in new row
 

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

Remove all duplicates and return highest value in new row
 

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



--
Gary''s Student - gsnu200828


"Nikkynock" wrote:

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

duplicates
 

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

duplicates
 

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.
--
Gary''s Student - gsnu200828


"nikkynock" wrote:

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

duplicates
 

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" wrote:

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.
--
Gary''s Student - gsnu200828


"nikkynock" wrote:

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

duplicates
 

5011111805106 is a valid number. Are any values more than 15 digits??
--
Gary''s Student - gsnu200828


"nikkynock" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200828


"nikkynock" wrote:

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

removing duplicates and returning highest value in new row
 

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