So the link I found cascaded into a solution but it took more than 2 formulas.

http://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html#a1

And here is a link to an example sheet I created:
https://drive.google.com/file/d/0B4hGhOqw8ilUOVdkMGVLLVR2Uzg/view?usp=sharing

This covers the Header migration, but in order to get to this point requires 2 more columns of data that need formulas as well.

for people who's header is static you need a way to get the headers into a column, my headers were already a transpose from another column so I had that done, but something like:

```
=OFFSET(FirstHeader,0,ROW()-CurrentRowNumber)&""
```

could get your column headers into a column for you.
So now you have:

```
ColumnHeaders
Column1
Column2
Column3
```

from:

```
Column1 Column2 Column3 BlankColumn BlankColumn
Data1 Data1 Data3
Data2 Data4
```

Now I needed a count of the number of data items in each column. Which I got from this LittleMonstrosity:

```
=IF(B2<>"",SUMPRODUCT(--(INDIRECT(ADDRESS(FirstRowIndex,MATCH(B2,$A$1:$AA$1,0))&":"&ADDRESS(LastRowIndex,MATCH(B2,$A$1:$AA$1,0)))<>"")),"")
```

Where the First and Last row index are a predefined bound for how large of lists I'm going to accept because of memory limits.Similar to the reasoning for only allowing A to AA for columns. Maybe I'll move my bounding box out later so I can change it from one location.

So using this I now get:

```
CountIndices ColumnHeaders
2 Column1
1 Column2
2 Column3
```

but I need actual start indices not just counts so I put 1 in the first row of the start indices and wrapped the little monstrosity in:

```
=IFERROR(A2+LittleMonstrosity,"")
```

to get:

```
StartIndices ColumnHeaders
1 Column1
3 Column2
4 Column3
6
```

Only now can I use the example code from the link I found to derive my first data column:

```
=VLOOKUP(ROWS($B$2:$B2),DataRange,2)
```

Once again with those bounds built into the DataRange. Soon I'll even make that dynamic within reason.

Now I have my first Header column:

```
A B M N
StartIndices ColumnHeaders Header Data
1 Column1 Column1
3 Column2 Column1
4 Column3 Column2
6 Column3
Column3
```

Now to fetch the data for each column we build a counter that counts the number of times the paired header appears in the column above it and uses that to index into the data after finding the correct column using this formula, keeping in mind that in this example The header is in the M column:

```
=IF(M2<>"",INDEX(DataRange,COUNTIF($M$2:$M2,M2),MATCH(M2,HeaderRange,0)),"")
```

Now I finally get:

```
A B M N
StartIndices ColumnHeaders Header Data
1 Column1 Column1 Data1
3 Column2 Column1 Data2
4 Column3 Column2 Data1
6 Column3 Data3
Column3 Data4
```