Conditional Formatting or Counting if within "x" of another value

[mbpaul1987]

I have a rather interesting situation that I can't seem to find an easy solution to. I have a few workbooks that have 3000+ lines of data in them. I need to find a way to pull a count of items that are within a certain distance of other items. As a very simplified example I will use the following table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Location (mile)[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]

I need to find a way to to count the number of "I" that are within "2 miles" (this number will be variable) of an S. In the case of the example there are 4. My Initial thought was to try to find a way to conditionally format the "I" rows that meet that criteria then I can easily sort them and count them. However, Ultimately I am just looking for an overall count of items within a set distance from another Item.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Location (mile)[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for any Help

Michael

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Item​

[/td][td]

Location (mile)​

[/td][td][/td][td]

Distance​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

I​

[/td][td]

1​

[/td][td][/td][td]

2​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

S​

[/td][td]

3​

[/td][td][/td][td]

4​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

B​

[/td][td]

3,5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

I​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

I​

[/td][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

S​

[/td][td]

9​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

I​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

B​

[/td][td]

21​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

I​

[/td][td]

22​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

I​

[/td][td]

24​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

S​

[/td][td]

26​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Distances in D2, D3...

Array formula in E2 copied down
=SUM(IF(IF(A$2:A$12="I",IF(ISNUMBER(TRANSPOSE(IF(A$2:A$12="S",B$2:B$12))),ABS(B$2:B$12-TRANSPOSE(B$2:B$12))))<=D2,1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Marcelo Branco]

A little simpler formula

E2 copied down
=SUM(IF(IF(A$2:A$12="I",ABS(B$2:B$12-TRANSPOSE(IF(A$2:A$12="S",B$2:B$12,9.99E+307))))<=D2,1))
Ctrl+Shift+Enter

M.

 

[mbpaul1987]

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Item​

[/TD]
[TD]

Location (mile)​

[/TD]
[TD][/TD]
[TD]

Distance​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

I​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

S​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

3,5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

I​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

I​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

S​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

I​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

B​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

I​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

I​

[/TD]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

S​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Distances in D2, D3...

Array formula in E2 copied down
=SUM(IF(IF(A$2:A$12="I",IF(ISNUMBER(TRANSPOSE(IF(A$2:A$12="S",B$2:B$12))),ABS(B$2:B$12-TRANSPOSE(B$2:B$12))))<=D2,1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Hi Marcelo,

Thanks for the reply! The solution works great for the small distances but now the issue that I'm having is when I get into longer distances that might overlap it may count the instance twice:
[TABLE="class: grid, width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]Location (mile)[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Distance[/TD]
[TD="width: 64"]Result[/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="width: 64"]S[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="width: 64"]B[/TD]
[TD="width: 64, align: right"]3.5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]S[/TD]
[TD="width: 64, align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]B[/TD]
[TD="width: 64, align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]I[/TD]
[TD="width: 64, align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64"]S[/TD]
[TD="width: 64, align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mbpaul1987]

Sorry this was a duplicate post. see above

 

[Marcelo Branco]

This is because some rows are being counted twice. For example, the distances from row5 (=4) to row3 (=3) and to row7(=9) are less or equal the value in D4(=6). If you do not want such double counting we need an alternative formula, that is:

E2 copied down
=SUM(--(MMULT(--(IF(A$2:A$12="I",ABS(B$2:B$12-TRANSPOSE(IF(A$2:A$12="S",B$2:B$12,9.99E+307))),9.99E+307)<=D2),ROW(A$2:A$12)^0)>0))
Ctrl+Shift+Enter


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Item​

[/TD]
[TD]

Location (mile)​

[/TD]
[TD][/TD]
[TD]

Distance​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

I​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

S​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

3,5​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

I​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

I​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

S​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

I​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

B​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

I​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

I​

[/TD]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

S​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[mbpaul1987]

This is because some rows are being counted twice. For example, the distances from row5 (=4) to row3 (=3) and to row7(=9) are less or equal the value in D4(=6). If you do not want such double counting we need an alternative formula, that is:

E2 copied down
=SUM(--(MMULT(--(IF(A$2:A$12="I",ABS(B$2:B$12-TRANSPOSE(IF(A$2:A$12="S",B$2:B$12,9.99E+307))),9.99E+307)<=D2),ROW(A$2:A$12)^0)>0))
Ctrl+Shift+Enter


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Item​

[/TD]
[TD]

Location (mile)​

[/TD]
[TD][/TD]
[TD]

Distance​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

I​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

S​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

3,5​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

I​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

I​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

S​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

I​

[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

B​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

I​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

I​

[/TD]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

S​

[/TD]
[TD]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.

Hi Marcelo,

That works perfectly. Thank you so much!! I must admit that array formulas are just not something that I have been able to fully wrap my head around and so to me they are about equivalent to magic . Once I'm done my current project I'm looking forward to doing a bit of research to properly understand how your formula works.

Again thanks so much!!

Michael

 

[Marcelo Branco]

Hi Michael,

You are welcome. Glad to help

M.