# Please I need urgent Excel code

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

• Replies 32
• Created

• 2

• 5

• 12

• 13

#### Posted Images

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

Edited by Kalsefar
##### 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

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

##### Share on other sites

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 by Kalsefar
##### 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

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
##### Share on other sites

1 minute ago, PeterPan9720 said:

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.

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

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

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

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

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

##### Share on other sites

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.

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.