Transform calendarized data

[Steve T]

I have a monthly reporting spreadsheet that is for the form:
Item Jan-09 Feb-09 Mar-09
X 10 10 12
Y 13 4 13
Z 12 11 9
A 44 14 23
L 51 2 50

What I need to do is transform the data in the monthly columns to a single
column and add a column that reflects the month that contains the data. The
form of the output needs to be:
Item Qty Date
X 10 Jan-09
Y 13 Jan-09
Z 12 Jan-09
A 44 Jan-09
L 51 Jan-09
X 10 Feb-09
Y 4 Feb-09
Z 11 Feb-09
A 14 Feb-09
L 2 Feb-09
X 12 Mar-09
Y 13 Mar-09
Z 9 Mar-09
A 23 Mar-09
L 50 Mar-09
I have been cutting and pasting. Not fun. Any suggestions on how to
transform the data

 

[Sean Timmons]

Sure..
Assume the table is in sheet1 and your results go to sheet2

In B2 of your Sheet2:

=INDEX(Sheet1!A1:Z5000),MATCH(A2,Sheet1!A:A),MATCH(C2,Sheet1!1:1)

 

[Steve Thornbrugh]

I was unable to get this to work. Could you elaborate a little more?

 

[Sean Timmons]

well.. INDEX looks at your table (Assuming A1:Z5000 here)

Then takes the value in a row you specify (Match your item letter to column
A of your table)

Then takes the value in a column you specify (Find your date in row 1 of
your table)

And gives the value at that intersecting point.

For the first value, it should find "X" in row 2

Then Jan-09 in column 2

And return the value at 2,2 of your arrray, 10.

Not knowing where your table is located in your workbook, I wouldn't be able
to give you the precise formula, but hopefully you can adjust as needed.

You would need to hvae your list of Items and Dates pre-populated...

That should only be a matter of copy and paste...

Use the Fx next to your address bar for guidance if needed...

 

[Pete_UK]

The formula assumes that you've already got X Y Z A L repeated down
column A of Sheet2, and for each block of these you have a date in
column C. The date must be in the same format as used for your header
rows in Sheet1.

It is possible to have a formula in A1 that would pick up the values
in column A of sheet1 and repeat them in blocks down the column, and
have another formula in C1 which would repeat the dates for as many
rows as you have entries in the block and then choose the next date,
etc, but you didn't say how many entries you have.

Hope this helps.

Pete