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.
<tbody>
</tbody></b20)+(c20<b19))
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>