Help with Indexing and/or Matching???

[Kwasi]

Here is some sample data:

22-Nov 36.5
23-Nov 28.2
24-Nov 29.2
25-Nov 34.3
26-Nov 31.9
27-Nov 36.4
28-Nov 41.8
29-Nov 34.5
30-Nov 31.0
1-Dec 30.3
2-Dec 37.0
3-Dec 39.8
4-Dec 36.9
5-Dec 32.8
6-Dec 35.7


I want to transform it into a table like this

Jan Feb .... Nov Dec
1 30.3
2 37.0
3 39.8
4 36.9
5 32.8
6 35.7
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 36.5
23 28.2
24 29.2
25 34.3
26 31.9
27 36.4
28 41.8
29 34.5
30 31.0
31


Could someone help me?

 

[Eric]

Here's one solution without using index or matching:

If you make the months across the top of the matrix you
are trying to create actual dates instead of just text
months, you can use something similar to the following
formula:


=VLOOKUP(DATE(YEAR(Monthly_Title),MONTH(Monthly_Title),DAY
(Day_Col)),Reference_Data,2,FALSE)

Monthly_Title = Dates across the top of the matrix (i.e.
11/1/2003)

Day_Col = Cells containing the day you want to reference
(1,2,3,4,etc.)

Reference_Data = Reference data going down in rows like
you specified in your original post.

I hope that helps!

Eric

 

[Ken Wright]

With your data in Cols A&B, starting in A1 with headers 'Date' / 'Amount', in
Col C put the header 'Day'. Now in C2 put =DAY(A2) and copy down.

Select all the data and do Data / Pivot table and chart report, hit Next, Next,
Finish.

Drag Day over to the left where it says ROW fields. Drag Date to the top where
it says Column fields, and then right click on any date and select GROUP and if
months is checked then also check Years (Leave Years out if you want all Jans
together, all Febs together etc). Now drag Amount into DATA area. - Done.