# Keep the bigget progressive number in a list VBA or Formula

#### jevi

##### Active Member
Dear All,

I have this two columns and I would like to keep only the biggest progressive number in column B based on the unique value of column A. I have a list of 30.000 rows and the progressive number of column be is from 1-105.

So the result I want is to the keep the biggest progressive number based on the unique Client Code so for the client 56097 I want to keep the second row with value 2, then for the client 56101 I would like to keep the 5 row with the value 3 in the Column B and so on.

Any help much aprecciated .
Thank you,

Column A Column B
Client Code Progressive Number

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### offthelip

##### Well-known Member
You haven't stated where you want the results so I have put them in columns C and D
try this code:
VBA Code:
``````Sub test2()
LastRow = Cells(Rows.Count, "A").End(xlUp).row
inarr = Range(Cells(1, 1), Cells(LastRow, 2))
outarr = Range(Cells(1, 3), Cells(LastRow, 4))
indi = 1
curv = inarr(1, 1) ' initail value
For i = 2 To LastRow
If curv <> inarr(i, 1) Then
outarr(indi, 1) = curv
outarr(indi, 2) = inarr(i - 1, 2)
curv = inarr(i, 1)
indi = indi + 1
End If
Next i
Range(Cells(1, 3), Cells(indi, 4)) = outarr

End Sub``````

##### Well-known Member
Try This. With CTRL+SHIFT+ENTER

Book1
ABCDEF
1Unique ItemsMax Value
2560971560973
3560972560992
4560973561014
5560991 0
6560992 0
7561011
8561012
9561013
10561014
11
Data USD
Cell Formulas
RangeFormula
D2:D6D2=IFERROR(INDEX(\$A\$2:\$A\$10,MATCH(0,COUNTIF(\$D\$1:D1,\$A\$2:\$A\$10),0)),"")
E2:E6E2=IFERROR(MAX((\$A\$2:\$A\$10=\$D2)*B2:B10),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### jevi

##### Active Member
hi OfftheLip,

My fault that I didn't explain well...I wanted to delete the other rows and keep only the one that give the result I want as I need to work with only that data.

The data are from column A to O but the one who decide to delete are according to the rule I mentioned above.

thank you

##### Well-known Member

@jevi Are you tested my formulas? What about them?

#### offthelip

##### Well-known Member
Try this:
VBA Code:
``````Sub test2()
LastRow = Cells(Rows.Count, "A").End(xlUp).row
inarr = Range(Cells(1, 1), Cells(LastRow, 15))
Range(Cells(1, 1), Cells(LastRow, 15))=""
outarr = Range(Cells(1, 1), Cells(LastRow, 15))
indi = 1
curv = inarr(1, 1) ' initail value
For i = 2 To LastRow
If curv <> inarr(i, 1) Then
outarr(indi, 1) = curv
outarr(indi, 2) = inarr(i - 1, 2)
curv = inarr(i, 1)
indi = indi + 1
End If
Next i
Range(Cells(1, 1), Cells(indi, 15)) = outarr

End Sub``````

#### jevi

##### Active Member

Maabi,
Sorry I was going to reply but had a busy day. They work perfectly as I tried them but the file is too big as has a lot of data and with the formulas takes too much time. Still, great to know them

#### jevi

##### Active Member
Try this:
VBA Code:
``````Sub test2()
LastRow = Cells(Rows.Count, "A").End(xlUp).row
inarr = Range(Cells(1, 1), Cells(LastRow, 15))
Range(Cells(1, 1), Cells(LastRow, 15))=""
outarr = Range(Cells(1, 1), Cells(LastRow, 15))
indi = 1
curv = inarr(1, 1) ' initail value
For i = 2 To LastRow
If curv <> inarr(i, 1) Then
outarr(indi, 1) = curv
outarr(indi, 2) = inarr(i - 1, 2)
curv = inarr(i, 1)
indi = indi + 1
End If
Next i
Range(Cells(1, 1), Cells(indi, 15)) = outarr

End Sub``````

Thank you Offthelip. Now I am home and I don't have the file but I will try it tomorrow and let you know. Thank you so much.

#### jevi

##### Active Member
Hi Offthelip,
I did try...it is almost perfect, but is deleting the other data that i have in the other columns after A and B column. I need them also. It needs to delete the rows as it does and leave the data in the other columns.

Sorry for confusing you.
Thank you

#### offthelip

##### Well-known Member
whoops sorry my mistake, try this:
VBA Code:
``````Sub test2()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(LastRow, 15))
Range(Cells(1, 1), Cells(LastRow, 15)) = ""
outarr = Range(Cells(1, 1), Cells(LastRow, 15))
indi = 1
curv = inarr(1, 1) ' initail value
For i = 2 To LastRow
If curv <> inarr(i, 1) Then
For j = 1 To 15
outarr(indi, j) = inarr(i - 1, j)
Next j
curv = inarr(i, 1)
indi = indi + 1
End If
Next i
Range(Cells(1, 1), Cells(indi, 15)) = outarr

End Sub``````

Replies
3
Views
75
Replies
1
Views
343
Replies
6
Views
126
Replies
2
Views
505
Replies
9
Views
178

1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

### 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.

### Which adblocker are you using?

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

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