Conditional Expression that Moves Data (challenging)

[themangoagent]

So this isn't your everyday conditional formatting, and that's why I need some help.

I have columns of data, and some of the data has a relationship with more than one value.

http://img246.imageshack.us/i/resultwanted.png/

I want to move data from the street column to a new column next to it so that you would have a result like this:




http://img51.imageshack.us/i/datab.png/
The problem, as you may see, is that there are sometimes multiple streets, and sometimes there aren't. The trigger would obviously be that there is no data located in the name cells, so we would know that there is an extra street address to be moved to a new column.

Thanks for any help at all! I really appreciate it!

[BjornS]

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