Excel (or VBA) Conditional Transposing Rows

Question

I have a Dataset below with months in the column headers, I'd like to duplicate the rows based on the months present. One issue I had is the automation, as users are constantly being added/removed.

UserID  User State Jan($) Feb ($) Mar ($) .... Dec ($) 
111     AAA   CT    $55    $100   $125         $100       
112     BBB   NJ    $50    $34    $125         $125  
113     CCC   NV    $55    $100   $125         $155  
114     DDD   VT    $95    $108   $75          $199  
115     EEE   NJ    $20    $100   $125         $120  

Sample Output:

UserID User State Month Spend
111    AAA   CT    Jan   $55
111    AAA   CT    Feb   $100
111    AAA   CT    Mar   $125
111    AAA   CT    Apr   $80
111    AAA   CT    May   $70
.
.
.
115    EEE   NJ    Nov  $50
115    EEE   NJ    DEC  $120

Answer 1

You didn't say Access, but here's how it could be done in Access. I don't think an Excel PivotTable can do this...?
You need sort-of the opposite of a Crosstab Query.
A Union query does this.
See if this example gets you moving in the right direction.

Answer 2

For a formula answer:

For the First 3 Columns:

In the first cell in the first column you want your data, mine is A11:

=INDEX(A:A,QUOTIENT(ROW(1:1)-1,12)+2)

For the Month:

Copy over two Columns, then copy down.

In the first cell in the fourth column in which you want the data, mine is D11:

=CHOOSE(MOD(ROW(1:1)-1,12)+1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Then copy down.

For the Amounts:

In the first cell in the fifth column in which you want the data, mine is E11:

=INDEX($A:$O,QUOTIENT(ROW(1:1)-1,12)+2,MOD(ROW(1:1)-1,12)+4)

Then copy it down.