VBA to output multiple rows from table with entries in single row

Hello All,

I have a project for my neighborhood to update our Neighborhood directory (online and printed). We have hundreds of homes and some "complex" living situations and we want to properly address each resident in our directory. Ideally we avoid having to manage two tables, as we have been doing...too much effort and possibilities for errors.

The directory is set up as a basic table, which has grown over time. The primary key is the address. In many cases we have residents at the same address with different last names and in our directory we have one section which is by last name, which means different last names need to be listed separately....I have been using pivot tables to do all my sorting so far and since their is only 1 row per address this creates a problem.

I believe the right approach is to generate an output table with some VBA, but I am struggling to find a good start. So I have attached an example file with the basic layout of our directory, some example data, and how the output is to be formatted.

Any suggestions??

Thanks,
Matt

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Thank You for changing the title.
This is not exactly the format you are looking for however it should take up the same amount of room as two rows should occupy the same space as a row that has two phone numbers wrapped.
This proposal employs a helper column and two helper tables all of which may be moved and/or hidden for aesthetic purposes.
The helper column provides the first row for each address entry in the second helper table using: =SUM(H4,2) where H4 contains the number 1.
The first helper table, highlighted yellow, is populated using: =IF(AND(COLUMN()=13,A3=""),F3,A3)
The second helper table, highlighted green, is populated using three formulas:
1. For addresses: =INDEX(I$4:I$7,MATCH(ROWS(A$1:A1),H$4:H$7))
2. For names: =IF(ISODD(COUNTIFS(J$19:J19,J19)),INDEX(J$4:J$7&", "&K$4:K$7,MATCH(J19,I$4:I$7,0)),INDEX(M$4:M$7&", "&N$4:N$7,MATCH(J19,A$4:A$7,0)))
3. For phone numbers: =IF(ISODD(COUNTIFS(J$19:J19,J19)),INDEX(L$4:L$7,MATCH(J19,I$4:I$7,0)),INDEX(O$4:O$7,MATCH(J19,A$4:A$7,0)))
The final table, highlighted blue, is populated using: =IFERROR(INDEX(I$19:I$26,AGGREGATE(15,6,(ROW($A$19:$A$26)-ROW($A$18))/($K$19:$K$26<>0),ROWS($A$1:$A1))),"")