Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
removing duplicates and returning highest value in new row
the max number of digits in the string is 13 - any ideas?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return next highest number in range | Excel Worksheet Functions | |||
Return highest number or a blank if 0 | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
Summing & return highest value | New Users to Excel | |||
How do I return the highest value in a range of cells | Excel Worksheet Functions |