Jump to content

Please I need urgent Excel code


Kalsefar
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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 by Kalsefar
  • Like 1
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

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 by PeterPan9720
Link to comment
Share on other sites

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. 

  • Like 1
Link to comment
Share on other sites

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 by PeterPan9720
Link to comment
Share on other sites

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, 

  • Like 1
Link to comment
Share on other sites

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?

  • Like 1
Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...