Keep the bigget progressive number in a list VBA or Formula

jevi

Active Member
Joined
Apr 13, 2010
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
1611141682840.png
 

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
Joined
Dec 23, 2017
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Apr 13, 2010
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Apr 13, 2010
Messages
266
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 13, 2010
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 13, 2010
Messages
266
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top