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

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
– BruceWayne, Commented Feb 12, 2016 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
– Porkythepig, Commented Feb 12, 2016 at 16:44

Scott Craner · Accepted Answer · 2016-02-12 16:58:33Z

3

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.

edited Feb 12, 2016 at 16:58

answered Feb 12, 2016 at 16:04

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

n8. Over a year ago

Need the month as well.

Scott Craner Over a year ago

@n8 yup I forgot it give me sec.

Scott Craner Over a year ago

@Porkythepig please mark the answer as correct by clicking on the green check mark by the answer. It is something only you can do.

BruceWayne Over a year ago

I've not seen Quotient() used with Index() before, I'll study that as it looks pretty convenient. Nicely done!

Scott Craner Over a year ago

@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.

|

VolleyballAddictSandiego · Accepted Answer · 2016-02-12 15:45:26Z

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.

edited Feb 12, 2016 at 15:45

answered Feb 12, 2016 at 15:32

VolleyballAddictSandiego

3931 gold badge2 silver badges11 bronze badges

1 Comment

Porkythepig Over a year ago

Thanks! I'm trying to see if I can do this in excel, as my access and SQL skills are limited.

Collectives™ on Stack Overflow

Excel (or VBA) Conditional Transposing Rows

2 Answers 2

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related