# Return offset value of overlapping values to a single cell

#### Akorax

##### New Member
Hello!

Excel version: 2010

I'm trying to compare a value range from a unit in a table with every other unit in that table to find out if there are any overlapping value ranges. The range to compare consist of a minimum value and a maximum value. If one or more range overlaps are found, the overlapping unit(s) will be named in a new colum.
The reason for this is to find out if the units need further analysis on other values/parameters (not named here) to distinguish them from eachother.

Below you can see a more concrete example of what I'm trying to accomplish with this marvelous program. The example is a small one but the list I want to create may grow up to 30 rows in size.

The formulas I've used this far are:
Cell D19 =NOT((C19<B20)+(C20<B19))
<b20)+(c20<b19))
Cell E19 =IF(D19,"Overlap","No overlap")

I figure this might be impossible to do without the assistance of VBA enabled content but I have a restriction not to use macros/VBA in this case.

 A B C D E 1 List I want to compare 2 Names Min Value Max Value 3 Unit A 10 20 4 Unit B 15 25 5 Unit C 30 50 6 Unit D 35 45 7 Unit E 40 60 8 9 Result I want 10 Names Min Value Max Value Overlapping with 11 Unit A 10 20 Unit B 12 Unit B 15 25 Unit A 13 Unit C 30 50 Unit D, Unit E 14 Unit D 35 45 Unit C, Unit E 15 Unit E 40 60 Unit C, Unit D 16 17 What I have tried 18 Names Min Value Max Value Overlap Overlap in plain text 19 Unit A 10 20 TRUE Overlap 20 Unit B 15 25

<tbody>
</tbody>
</b20)+(c20<b19))

#### Akorax

##### New Member
HTML screwed my formulas big time.
Here they are uncut (hopefully) this time:

Cell D19 =NOT((C19<b20)+(c20<b19))
Cell E19 =IF(D19,"Overlap","No overlap")</b20)+(c20<b19))

1,081,678
Messages
5,360,462
Members
400,586
Latest member
Minty

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...