Please I need urgent Excel code

[PeterPan9720]

6 minutes ago, lrm said:

Please ignore my last post!  I did not finish it.  Need to  compare successive coordinate pairs but need to work on something else right now.

I guess @kalsefar now need a way to find "similar" cell value, because with countif and procedure I showed him, he solved the duplicated value, I guess your code should be rearrange in order to do this, but many time asked which is the value difference from a cell to another in order to define them similar but I never received answer.

Look at post exchanged.

[Kalsefar]

Dude,
Believe me, I have good knowledge about VBA, I tried to work with your solution but I failed maybe there is a misunderstanding, my requirements is a way that's can help me to compare and find the  similar values like :

21,649004	39,398330
21,649005	39,398329

 

As you can see, the numbers are the same except for the sixth number after the comma, it is different

 

If you can write a code to do that, please attach the excel file that's will be kind of you

@PeterPan9720

Edited October 29, 2020 by Kalsefar

[lrm]

Let's say you have a 2 dimensional array named LatLong where the first index is the data number and the second is 1 for latitude and 2 for longitude.

i and j are indices indicating which data readings you want to compare.

Comparing the two sets of coordinates as follows should tell you if the are "similar" (equal to the number of specified decimal places).  

 

n = InputBox("Specify the number of decimal places for comparison." , "Specify Precision", 6)
a = Round(LatLong(i, 1), n)
b = Round(LatLong(j, 1), n)
c = Round(LatLong(i, 2), n)
d = Round(LatLong(j, 2), n)
If a = b And c = d Then
        ' we have a duplicate
Else
        ' not a duplicate
End If

 

[lrm]

I think the following works.  I tested it on your data for 4, 5, and 6 decimal places.

Option Base 1
Sub test()
'Removes Lat Long coordinates pairs that are duplicated within
' a spedified precision.
'LRM 10 / 29 / 2020 ver 1
Dim LatLong(100, 2) As Variant
Dim n As Integer, i As Integer, k As Integer, num As Integer
Dim a As Double, b As Double, c As Double, d As Double
i = 1
msg = "Please enter desired number of decimal places" & vbCrLf & _
"of desired precision."
n = InputBox(msg, "Specify Precision", 6)
Range("B2:B2").Select
While ActiveCell.Value <> ""
    LatLong(i, 1) = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    LatLong(i, 2) = ActiveCell.Value
    ActiveCell.Offset(1, -1).Select
    i = i + 1
Wend
num = i - 1
LatLong(1, 1) = LatLong(1, 1)
LatLong(1, 2) = LatLong(1, 2)

For i = 1 To num - 1
 k = 0
    For j = i + 1 To num
        a = Round(LatLong(i, 1), n)
        b = Round(LatLong(j, 1), n)
        c = Round(LatLong(i, 2), n)
        d = Round(LatLong(j, 2), n)
        If a = b And c = d Then
                ' we have a duplicate
                k = k + 1
        Else
                LatLong(j - k, 1) = LatLong(j, 1)
                LatLong(j - k, 2) = LatLong(j, 2)
        End If
    Next j
Next i
' Output results
Range("E2:F101").Select
Selection.ClearContents
Range("e2:e2").Select

For i = 1 To num
    ActiveCell.Value = LatLong(i, 1)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = LatLong(i, 2)
    ActiveCell.Offset(1, -1).Select
    If Round(LatLong(i, 1), n) = Round(LatLong(i + 1, 1), n) And _
        Round(LatLong(i, 2), n) = Round(LatLong(i + 1, 2), n) Then
        i = num
    End If
Next i
End Sub

 

[Kalsefar]

Thank you guys, @PeterPan9720 @lrm

 

I found a way to help my requirements, but I need VBA code to sort data automatically from largest to smallest or vice versa

Edited October 30, 2020 by Kalsefar

[PeterPan9720]

12 minutes ago, Kalsefar said:

Thank you guys, @PeterPan9720 @lrm

 

I found a way to help my requirements, but I need VBA code to sort data automatic from largest to smallest or vice versa

@Kalsefar

So due to are expert with VBA, you should know that excel allows you to record a macro while you are making action, so I suggest to start a macro recording, do the sort as you wish, ascendant or descendant, and retrive the code you require at the end of the actions.

In any case your first request was to search duplicated value, and seems you solved your issue, later you are asking of find similar value that is another kind of issue.

In addition, even if I'm not forum moderator, I would like to highlight that this is not an Excel Forum, but based on Autocad customization by VBA or .NET

Edited October 30, 2020 by PeterPan9720

[Kalsefar]

1 minute ago, PeterPan9720 said:

@Kalsefar

So due to are expert with VBA, you should know that excel allows you to record a macro while you are making action, so I suggest to start a macro recording, do the sort as you wish, ascendant or descendant, and retrive the code you require at the end of the actions.

In any case you first request was to search duplicated value, and seems you solved your issue, later you are asking of find similar value that is another kind of issue.

 

 

Dude since the beginning I was asking for a cod to help me to find similar values, I wonder why you didn't get my point, by the way.

I appreciate your kind help. 

[PeterPan9720]

3 minutes ago, Kalsefar said:

 

 

Dude since the beginning I was asking for a cod to help me to find similar values, I wonder why you didn't get my point, by the way.

I appreciate your kind help. 

Dude, please look at your post, you are searching in the first time only for duplicated value, and this is a forum not a company payed for solve your issue.

Bye

Edited October 30, 2020 by PeterPan9720

[Kalsefar]

2 minutes ago, PeterPan9720 said:

Dude, please look at your post, you are searching in the first time only for duplicated value, and this is a forum not a company payed for solve your issue.

Bye

 

chill out, 

[eldon]

47 minutes ago, Kalsefar said:

........I found a way to help my requirements, but I need VBA code to sort data automatically from largest to smallest or vice versa

 

Are you not allowed to use the inbuilt Sort function in Excel?

[Kalsefar]

15 minutes ago, eldon said:

 

Are you not allowed to use the inbuilt Sort function in Excel?

 

It's allowed, but I'm creating a program so I need all functions to work automatically

[lrm]

Here's VBA code for sorting using the bubble method.

Sub Bubble(n, a, b)
'sorts an array in ascending order using the bubble sort method
'n: the number of elements to be sorted (input)
'a: the unsorted array (input)
'b: the sorted array (output)
' from Chapra, "Power Programming with VBA/Excel", p 156
' modified by LRM 4/10/2009 to work with Option Base 1 or Base 0
'
Dim m As Integer, i As Integer
Dim switch As Boolean
Dim dum As Double, Base As Integer
Base = 0
' test if option base is 0 or base 1
If LBound(a) = 1 Then Base = 1
'copy a array to b array
For i = Base To Base + n - 1
   b(i) = a(i)
Next i
m = Base + n - 2 
Do ' loop through passes
   switch = False
   For i = Base To m ' loop through array
      If b(i) > b(i + 1) Then
         dum = b(i)
         b(i) = b(i + 1)
         b(i + 1) = dum
         switch = True
      End If
   Next i
   If switch = False Then Exit Do
   m = m - 1
Loop
End Sub

 

[Juanma]

171/5000

 

 

 

I give you an excel that does what you ask.
once the data is included, you just have to copy-paste as values, the two columns with the good data.

Hope this can help you

Test-resuelto.xlsx