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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name 1[/TD]
[TD]Address 1[/TD]
[TD]Age 1[/TD]
[TD]Name 2[/TD]
[TD]Address 2[/TD]
[TD]Age 2[/TD]
[TD]Name 3[/TD]
[TD]Address 3[/TD]
[TD]Age 3[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]My Home[/TD]
[TD]50[/TD]
[TD]Mary[/TD]
[TD]Your Home[/TD]
[TD]40[/TD]
[TD]James[/TD]
[TD]Our Home[/TD]
[TD]70[/TD]
[TD]01/31/1991[/TD]
[/TR]
</tbody>[/TABLE]



and, I want it to look like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Age[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]My Home[/TD]
[TD]50[/TD]
[TD]01/31/1991[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Your Home[/TD]
[TD]40[/TD]
[TD]01/31/1991[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Our Home[/TD]
[TD]70[/TD]
[TD]01/31/1991[/TD]
[/TR]
</tbody>[/TABLE]



Thank you !