1

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
Porkythepig
  • 23
  • 1
  • 4
  • 1
    What have you tried? Once you get your output table set up with the months going down, you can use an index/match or vlookup to return the other info. Please let us know what you've attempted so far, and what has or hasn't worked. – BruceWayne Feb 12 '16 at 15:20
  • I've been able to manually duplicate it, but the table is dynamic and users are added/removed frequently, as well as spend. Been playing around with pivot table settings, but the months just won't pull as intended. – Porkythepig Feb 12 '16 at 16:44

2 Answers2

3

For a formula answer:

![enter image description here

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Need the month as well. – n8. Feb 12 '16 at 16:12
  • @n8 yup I forgot it give me sec. – Scott Craner Feb 12 '16 at 16:14
  • @Porkythepig please mark the answer as correct by clicking on the green check mark by the answer. It is something only you can do. – Scott Craner Feb 12 '16 at 17:04
  • 1
    I've not seen `Quotient()` used with `Index()` before, I'll study that as it looks pretty convenient. Nicely done! – BruceWayne Feb 12 '16 at 17:11
  • @ScottCraner Just did. I also noticed that the Index() Quotient() formula doesn't work if there's an extra blank row in column 1. I tried shifting all the formula down one, but it just duplicates 'ID' 11 times. Do I have to lock one of the formulas to compensate? – Porkythepig Feb 12 '16 at 19:15
  • @Porkythepig You are correct if there is a blank row in the data then there will be 12 blank rows in the output. The formula to adjust for that is daunting and will be an array formula which will make if very slow to calculate. Where is the blank,in the middle, or at the top? Is it just one row, or many? – Scott Craner Feb 12 '16 at 19:18
  • @ScottCraner it's at the top. if it's too complicated i will remove the top blank row, it was there for non-essential purposes. – Porkythepig Feb 12 '16 at 19:42
  • 1
    @Porkythepig if it is at the top then change the `+2` to `+3` or `+4` change it to the first row number in which the data is. – Scott Craner Feb 12 '16 at 19:45
  • Two mini notes to quickly understand the _Amounts_ part: `$A:$0` means the columns where your data is (in the example `A` _ID_ is the first row of the data and `O` _Dec_ is the last one). The `+4` part here means the column where the data you need is (in the example is column `D` where the _$_ values are). I invested some minutes figuring this out. I hope this is helpful. – Metafaniel Nov 02 '19 at 01:46
0

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.

enter image description here

enter image description here

VolleyballAddictSandiego
  • 363
  • 2
  • 10