Geek Answers Handbook

How to save only the LAST value of each pair in excel VBA?

Contract No Price D/019/09 17.85 D/019/09 17.85 D/019/09 17.85 D/019/09 17.85 D/023/09 17.85 D/023/09 17.85 D/026/09 0 D/026/09 0 D/038/11 20.6 D/038/11 20.6 D/038/11 20.6

The above is an example of a data series ... and I will love to leave only the LAST cases of each unique contract no. In the above example, I would like to save only D / 023/09 17.85, D / 026/09 0 and D / 038/11 20.6. Thus, there should be only 3 unique values. What is the most efficient way to do this?

+4
source share
3 answers

In addition to my comment, there is another way here that does not require sorting your data . See Example D/019/09

 =IF(COUNTIF($A2:$A$13,A2)>1,0,B2)

enter image description here

+1
source

There is no vba ... Add column C and name it "Check." Paste the following formula into cell C2:

 =if(A2<>A3,1,0)

Then copy this formula to the end of the data range. Then apply a filter to 0 in column C. Delete all the rows and you have.

Your leaflet before:

enter image description here

Your sheet after:

enter image description here

0
source

Try entering the code below. Using vba

enter image description here

 Sub sample() Dim lastRow As Long, j As Long lastRow = Range("A65000").End(xlUp).Row j = 1 For i = lastRow To 2 Step -1 If Cells(i, 1).Value <> Cells(i + 1, 1) Then Cells(i, 1).Copy Cells(j, 3) Cells(i, 1).Offset(0, 1).Copy Cells(j, 4) j = j + 1 End If Next End Sub
0
source

More articles:


All Articles