1

I need to perform a transpose with a few static columns (Fruit, Category) which can vary. Also the Period can go up to 15 years. I have tried the Transpose feature, but this can transpose an individual ROW or COLUMN whereas I want to achieve the target below.

Current State

Fruit     Category     2012    2013
Apple     Fruit        24      26
Orange    Fruit        20      32
Tomato    Vegetable    50      40

Target State

Fruit  Category   Period  Quantity
  Apple  Fruit     2012    24
  Apple  Fruit     2013    26
  Orange Fruit     2012    20
  Orange Fruit     2013    32
  Tomato Vegetable 2012    50
  Tomato Vegetable 2013    40

I am not sure if there is a standard feature in Excel that can achieve this or if there is a trick with the transpose function itself.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kfactor21
  • 412
  • 5
  • 14
  • There is no standard feature or trick in transpose to do this. You can either develop complex formulas, or use a VBA solution. For the latter, I would suggest developing a user defined object with properties of fruit, category, year and quantity; collecting these objects and then writing them out in your desired format. – Ron Rosenfeld Sep 06 '15 at 17:17

1 Answers1

0

I think there is a standard 'trick' (unpivot with multiple consolidation ranges). See here. Assuming Fruit is in Sheet1!A1, in the new sheet in B2 of an inserted B column: 

 =VLOOKUP(A2,Sheet1!A:B,2,0)

Then select all, Copy, Paste Special..., Values, filter to select Category in ColumnC, delete all visible rows other than first, unfilter and rename column labels.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139