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:

	A	B	C	D	E	F	G	H
7/1/2015	1500			2400		3600
7/2/2015	1500		1500			3600
7/3/2015		1600		2400		3600
7/4/2015					1600	3600		1800
7/5/2015				2400		3600	1600
ETC....

<tbody>
</tbody>

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,

7/1/2015	1500
7/1/2015	2400
7/1/2015	3600
7/2/2015	1500
7/2/2015	1500
7/2/2015	3600
7/3/2015 ETC...	1600

<tbody>
</tbody>

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