Hello,

I work for a manufacturing company and use excel quite a bit. Someone came to me with a request and I've mulled it over for a few days and played around in Excel but I am unable to get it to do what I want it to do!

Essentially, as we manufacture items they are put into batches, individually serialized, and then tested. Somewhere along the line the program that does serial numbers has created duplicate serials numbers (It could be several thousand as there are A LOT of parts).

The serial numbers are stored in ranges.

For example, in column A there might be:

1001-1020
1001-1020
1021-1031
1032-1094

When the duplicate ranges are perfectly matched, say 1001-1020 and another instance of 1001-1020, I was able to create a countif function that returned how often it showed up thereby indicating it was a duplicate. The issue I am having is for the ranges that overlap each other. For example, a range from 1001-1020 and a second range from 1015-1025. The serials 1015, 1016, 1017, 1018, 1019 and 1020 are all duplicates in this scenario. I need some way of getting excel to let me know that those duplicates exist. I was playing around with some pretty big formulas that had greater than/less than with multiple if statements. I was able to get results when it was being compared to only one other serial number. Since there are several thousand it has to have a way of looking at all of those and returning a count or indicator that there are duplicates.

Any help would be appreciated.