Return offset value of overlapping values to a single cell


New Member
Jul 22, 2014

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))
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. :(

1List I want to compare
2NamesMin ValueMax Value
3Unit A1020
4Unit B1525
5Unit C3050
6Unit D3545
7Unit E4060
9Result I want
10NamesMin ValueMax ValueOverlapping with
11Unit A1020Unit B
12Unit B1525Unit A
13Unit C3050Unit D, Unit E
14Unit D3545Unit C, Unit E
15Unit E4060Unit C, Unit D
17What I have tried
18NamesMin ValueMax ValueOverlapOverlap in plain text
19Unit A1020TRUEOverlap
20Unit B1525



New Member
Jul 22, 2014
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))

Forum statistics

Latest member

Some videos you may like

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