Finding duplicates within a number range of another line

I have a large data set of ranges of a transport network.

I would like to find duplicates on the Start Miles/yards and End Miles/yards. This is easy enough with a unique tool. However, I also want to find duplicate entries that fit inside the widest range, for example.

Start mile 29 start yard 770 end mile 29 end yard 109, if there was an entry such as start mile 29, start yard 769 and end mile 29 and end yard 110 I would want to highlight this as a duplicate.

The key unique fields would be the "ELR", "TID", "Direction" fields that must match on each occassion to observe a duplicate.

I am stumped on this one and would really appreciate your help.

David.

2026-03-06_14h45_53.png

Datasets

Accepted answers

jrlindem

Hey, circling back around...

In thinking about your latest response with the picture thank you), I think you could use the basis of what I provided, but add in a multi-row-formula tool to evaluate the fourth row, for example and compare the starting mile/yard against the first rows ending mile/yard to flag it as being outside the original range. In that way, abandoning Rank Tool and opting to handle that logic exclusively inside a MRF Tool could solve it for you.

Sorry I don't have time to build that out for you, but I think you can get there using this approach. -Jay

All comments

jrlindem

Sounds like you need to consider rounding to the 10's position and then applying your deduplication after that. In this way the records are treated as "the same"

If you need, I can mock this up, but figured i'd give you a quick response to get you started. -Jay

Kearnd967

Hi, thanks for the quick response, how would this work please?

Kearnd967

The number ranges could be very wide or very short,

jrlindem

@Kearnd967

Okay, so depending on your need... I assumed you wanted to round the "tens" position. I built the attached (and shown) workflow to handle three different types of "rounding".

Floor forces the values to step up only once the number crosses a tens value

Ceiling caps the value until it breaches the next tens value

Rounding will go either up or down depending (normal rounding rules but at the tens position)

In this way, you can pick (or adapt) what you need. Once you have that, use SELECT to pick the right transformed field (omitting the original un-rounded number) and then either use Summarize+GroupBy or Unique Tool.

Workflow example is also attached. Hope this helps, -Jay

jrlindem_example.yxmd

Kearnd967

Thank you for this, appreciate the support so far. How will I use this to identify duplicates within the same dataset? I would need to use the start miles and yards combined and end miles and yards combined to set the range and find the relevant lines within the same data set that fall within that range. How could I use this model for this scenario?

David.

jrlindem

Ya, so you could apply the same "rounding" of your choice to the values and then use Summarize Tool to Group by those "normalized" ranges and then you can [Count] the number of records that fall within that grouping. That'll give you the count of duplicates.

If you need to pick one and then do something with the rest, then you could sort or rank the records, starting the count over with each new grouping based on your rounded values. Then you can use filter to do something with Record = 1 and something different with the remainder (Record <> 1).

Does that help? Otherwise, attach and excel file with fake data showing the before and after of what you want and I can mock something up. -Jay

Kearnd967

I have attached some sample data, you will see that the orange line is the unique line, but the two yellow lines fit within the orange line. I would like to highlight the orange as unique record 1 and the duplicates as duplicate records linked to the orange line. My original data has c150k rows and many clusters of duplicates.

Sample Data.xlsx

Kearnd967

ELR, TID and direction are the unique groupings.

jrlindem

Tell me more about [ECM8 | 2100 | (D)]

Help me with the following:

What makes the orange line the original? What rules identify it as the original
Are all other records the duplicates?
1. If so, why isn't the first line (non-highlighted a duplicate)
2. If not, why is the first line different despite the shared key?

Need more specifics on how you're categorizing each row in this example. -Jay

Kearnd967

Thanks Jay,

ECM8 and 2100 are the section identifyers, then you have the start mile and start yard which is the start location, you then have the end miles and end yards which is the finish point of the section.

So the orange lines starts at Miles/yards 54.1106 and ends at 0.1500

the last yellow line starts at the same placeas the orange line 54.1106 but ends part way through at 41.291

the second yellow line then starts at 40.1033 and ends at the orange line finsih point 0.1500

therefore the orange line encompasses both of the yellow lines and therefore the yellow lines are not required.

Hope this helps.

Kearnd967

Thanks Jay,

ECM8 and 2100 are both section identifiers. So they relate to the section of the area in question. The miles and yards should be used in conjunction "miles.yards"

The orange line starts at miles/yards 54.1106 and ends at miles/yards 0.1500

The two yellow lines are encopassed within the orange line

54.1106 -> 41.291 and 40.1033 -> 0.1500 and are therefore duplicates that are covered by the orange line.

The white line is a separate elelement of the section that is not covered by the orange line 0.1500 -> 0.1042

Ideally I would like to see the duplicates and which original line they are covered by.

Hope this helps

jrlindem

I still think I'm missing aspects of the logic that you'll need to apply, but now that I'm understanding the relationship between the numbers as distances... I wonder if you could simply sort by the Start Miles/Yards and then rank them. This results in the Rank = 1 as the original (within your Group By) and then the rest are Duplicates?

This is where i'm fearing there's more to consider with respect to the Ending Miles/Yards. But I mocked it up like this:

The upper path is just showing how you can create a normal rank using the Record ID Tool, and the bottom path is using the Rank Tool. I've also attached the workflow for reference. But since I switched to ranking, I did need to bump up a version so hopefully you have those tools as well.

Sorry if I'm not fully grasping the logic that's needed to do what you want, but hopefully this gives you some ideas to play around with? -Jay

jrlindem_example_2.yxmd

Kearnd967

Thanks Jay,

This gives me an idea, to rank and then crosstab and use some if formulas to identify duplicates.

The solution you provided does the first part, but the last yellow line is not a duplicate as it is outside of the range of the first line.

Appreciate all the help.

David.

jrlindem

Sorry I don't have time to build that out for you, but I think you can get there using this approach. -Jay

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703