Help with Indexing and/or Matching???

K

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?
 
E

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
 
K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top