Date Auto-fill via numbers in external table

[jordanblair07]

Hello,

I am not completely sure if this is possible, I have a table like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[TD]3600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD]3600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/3/2015[/TD]
[TD][/TD]
[TD]1600[/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[TD]3600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/4/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1600[/TD]
[TD]3600[/TD]
[TD][/TD]
[TD]1800[/TD]
[/TR]
[TR]
[TD]7/5/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2400[/TD]
[TD][/TD]
[TD]3600[/TD]
[TD]1600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ETC....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If a cell in column A - H has a number I want it to populate the date that the number corresponds with to another table. So if columnds A - H have 3 cells filled with numbers I should have three lines dated with 7/1/2015. Example below,

[TABLE="width: 500"]
<tbody>[TR]
[TD]7/1/2015[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]2400[/TD]
[/TR]
[TR]
[TD]7/1/2015[/TD]
[TD]3600[/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]7/2/2015[/TD]
[TD]3600[/TD]
[/TR]
[TR]
[TD]7/3/2015 ETC...[/TD]
[TD]1600[/TD]
[/TR]
</tbody>[/TABLE]

Then if a number is removed or additional number is added on that day the second table adjusts to include date and number.

I would prefer this to be done with a formula over a macor but what would be the easiest and most flexible way, so if changes are made a lot on the table the second table can adjust easily.

Thank you for the assistance in advanced!!!

 

[onlyadrafter]

Hello,

not sure if formula is possible, but macro below should work. Have assumed your dates are in Column A and values are in B to I.

Sub REORDER_TABLE()
     Sheets("Sheet2").Columns("A:B").ClearContents
    Application.ScreenUpdating = False
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row
        For MY_COLS = 2 To 9
            If Not (IsEmpty(Cells(MY_ROWS, MY_COLS).Value)) Then
                With Sheets("Sheet2")
                    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Cells(MY_ROWS, 1).Value
                    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Cells(MY_ROWS, MY_COLS).Value
                End With
            End If
        Next MY_COLS
    Next MY_ROWS
    Application.ScreenUpdating = True
End Sub

you can run this code whenever required.

 

[jordanblair07]

This is great!! Works how I want it to, thank you!!

Question for future reference, If I were to change were the dates were located, from the side to the top with the numbers going down. How would I change the macro? I tried adjusting myself but I am missing something, thank you for the help!

 

[onlyadrafter]

Hello,

does this work as expected?

Sub REORDER_TABLE_2()
    Sheets("Sheet2").Columns("A:B").ClearContents
    Application.ScreenUpdating = False
    For MY_COLS = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        For MY_ROWS = 2 To Cells(Rows.Count, MY_COLS).End(xlUp).Row
            If Not (IsEmpty(Cells(MY_ROWS, MY_COLS).Value)) Then
                With Sheets("Sheet2")
                    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Cells(1, MY_COLS).Value
                    .Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet1").Cells(MY_ROWS, MY_COLS).Value
                End With
            End If
        Next MY_ROWS
    Next MY_COLS
    Application.ScreenUpdating = True
End Sub