Find row differences

[#1]

Hello, 

I am working on a file and I need to determine if any of the numbers in the 'Pos UCN' column are within 10 of any other row in that column. For example, rows 6344 and 6345 are within 10 of each other. How do I identify that? I know that this will create a new column, which is what I want. I have spent 2 days on the user community trying various things and have not had any luck. 

the difference between the rows. I believe this would creat a new column of 'difference' between the. I would also like to identify (in another new column)

[#2]

What do you want to do with the values that contain characters?

[#3]

They have to be character values because I need the leading 0's. 

[#4]

I was talking about these:

[#5]

Hello,

To get the difference between 2 rows, then you have the formula below.

If you want to make sure that your line is at least 10 away from all the other lines, you can do this as follows:

- Convert your column into a number (see what we do with characters)
- Arrange your column in ascending order
- Create the Diff column using the formula above and check if there are some diff < 10

[#6]

Thank you. This gives me a count difference between rows. Now how do I highlight rows that a difference of less than 10?

[#7]

In the same way, you can create a formula as below to get if the difference is less than 10 (1) or not (0) in a new column

And use color values property to highlight rows with less than 10 of difference

[#8]

You can also set the rowstate to selected for the rows you find that have met the criteria

dt << select rows(.....)

[#9]

So I was able to make my two additional columns (Diff and Prev UCN <=5). But I want to be able to get both the numbers. So for row 6345, it identifies as a previous row within 5, but I want to also identify that row before (6344)? This is looking for a "Pos UCN" +/- 5 to see if there are numbers that are close together. If they are close together, I want to identify both so I can look further and determine any potential issues. 

[#10 · Accepted solution]

One option is to change the Diff column to perform the comparison

If(Dif(Num(:Pos UCN), 1) <= 5 | Abs(Dif(Num(:Pos UCN), -1)) <= 5,
	1,
	0
)

Or just calculate minimum difference and then perform check after

Min(Dif(Num(:Pos UCN), 1), Abs(Dif(Num(:Pos UCN), -1)))