Merge Data From Multiple Columns - Sharepoint Survery Results - VBA

[jfs2j]

Hello,

I am using SharePoint to collect data through its survey feature. However, I want to make the output data more manageable. As such, I want to take the multiple columns and consolidate them into a few.

Currently, it looks like this:

Name 1 Address 1 Age 1 Name 2 Address 2 Age 2 Name 3 Address 3 Age 3
John My Home 50 Mary Your Home 40 James Our Home 70


I want it to look like this:

Name Address Age
John My Home 50
Mary Your Home 40
James Our Home 70


The survey is continuously updates, so, ideally a macro/vba that extract the data from one sheet and produces it this way would be better.

Also, a word of caution is that sometimes the cells in either column may be empty. So I want to make sure the formula just doesn't stop because it sees a blank cell, but rather copy from top to bottom from one column to the other.

Last but not least, there is other data in the cells, with other steps I would probably add to the macro, but this is the bulk of the steps I couldn't figure.

Thanks in advance for your help !

 

[jfs2j]

Forgot to add a uniform date entry

So, Currently, it looks like this:

Name 1	Address 1	Age 1	Name 2	Address 2	Age 2	Name 3	Address 3	Age 3	Date
John	My Home	50	Mary	Your Home	40	James	Our Home	70	01/31/1991

<tbody>
</tbody>

and, I want it to look like this:

Name	Address	Age	Date
John	My Home	50	01/31/1991
Mary	Your Home	40	01/31/1991
James	Our Home	70	01/31/1991

<tbody>
</tbody>

Thank you !