MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP? PIVOT TABLE? WHAT TO USE?


Posted by Michael on October 28, 2001 5:58 PM

Hi,

I will be amazed -- and grateful -- if anyone knows the answer to this.

I have a worksheet with three columns of information: Date, TV Station Name, and Cost. There are about 400 unique station names, but not every station had an airing (and therefore a Cost entry) on every date. The data in the rows is currently sorted by date. Here's an example of the first few lines:

Worksheet 1
A B C D
Date Station Cost
1 1-Feb KBBB $100
2 1-Feb KCCC $200
3 2-Feb KAAA $150
4 2-Feb KBBB $300
5 2-Feb KDDD $250
6 3-Feb KCCC $ 50

In a second worksheet, I would like to arrange the foregoing data differently, like this:

Worksheet 2
A B C D E F G
1-Feb 2-Feb 3-Feb 4-Feb 5-Feb
1 KAAA 150
2 KBBB 100 300
3 KCCC 200 50
4 KDDD 250
5 KEEE
6 KFFF

What I'd like to say to Excel is: "For each cell in the 1-Feb column of Worksheet 2, take the station name in that row and see if it exists in the station column of Worksheet 1; if it does, AND if it exists in a row in Worksheet 1 where the date equals 1-Feb (the date atop the column in Worksheet 2), then return the cost value in that row (i.e., from Column D on Worksheet 1)." (I'd then like to copy that formula across the entire matrix I've made in Worksheet 2.)

Put another way, I'd like to say to Excel: "For each cell in Worksheet 2, please return the corresponding value from the Cost column in Worksheet 1 if, on Worksheet 1, there was an airing on the particular station listed on Worksheet 2 on the date atop the column in Worksheet 2. If not, leave the cell blank."

I have no idea how to get Excel to do this, so I would very much appreciate anyone's help. (I understand how VLOOKUP works, and am using it elsewhere in my workbook, but I have a feeling that it isn't enough to do this.)

(If it's easier to explain by telephone, I'd be happy to call you!)

Thanks!

Michael
[email @address removed]


Posted by Richard S on October 28, 2001 8:43 PM

PIVOT TABLE

I don't know that you'd want to call me in Austrlia. It looks like a pivot table is what you want. Select a cell in your data list, and select Pivot table from the data menu. Follow the steps until you are asked to drop various data items. Drag Date to the Column fields area, Station to the Row fields area, and Cost to the data area. This should give you the cost matrix you are after

Is this what you want?
Richard

Posted by Michael on November 05, 2001 2:23 PM

Re: PIVOT TABLE

Thanks! (Just after posting this, I read the pivot table section in Excel Help, and it is doing the trick just fine.)

Michael