Hi and thanks for the challenge :-)
After some hard formula work I think I have a solution for you!

I attach a file with the solution.

Except for what you see in the formulas I have some additional info.
- I have given area "=Data!$N:$S" the defined name "StreetData"
- As you see this solution works even if you have two different Richard Randolph
- The formula in cell D2 is written is such a general form, that it can be copied down (as far as needed) and right (as far as you have streets in the header, i.e. until column M in my example).

To give the final result, you just have to use normal filter, and select rows where Lastname = (filled).

I hope this solves your issue!

Kind regards
Bjorn

Attached Files

Streets in different columns.xls (48.5 KB, 17 views)