Hi,
I have an excel workbook with two sheets.Both sheets have 'item number
as a column.Both sheets contain different number of rows.I want t
write a macro for displaying the common items in both sheets on a pag
(comparing both the sheets and getting the common item numbers)

I have to write a macro for generating the Waterfall char
attached(Waterfall.doc).The 'Actual' values come from one sheet and th
'Forecast' value from the other of the same worksheet.(Item 1,Ite
2......) are the common items in both the sheets.Varianc
=Actual-Forecast.........

Does this actually need a macro or can be done using a simple exce
worksheet ?



Attachment filename: waterfall.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62013
--
Message posted from http://www.ExcelForum.com

I don't think you need a macro.

How is the layout of the data in the sheets?
Item 7/16/2004 7/23/2004 7/30/2004 ...
123 400 200 300 200
234 400 300 50 100

or is it

Item Date Actual ...
123 7/16/2004 400
123 7/23/2004 200
123 7/30/2004 300
234 7/16/2004 300
234 7/23/2004 500
234 7/30/2004 100

In any case:

Copy/Paste data from Actual sheet into a new sheet, sheet3.
Add a column called SOurce that you fill up with the word Actual
Copy/Paste data from Forecast into sheet3 after previously pasted data and fill the Source column with the word Forecast.

Using a pivot chart/table.
-Now from these data, create a pivot table & chart (menu Data PivotTable & PivotChart)
-In the pivot , put the field 'Item' in the Page field area. This will create a dropdown box for which you can choose a specific item.
-put the field 'Source' in the row field area.
-then depending on the data layer in the first part of the post , complete the pivot by putting either:
-Layout2: 'Date' in the column field are and the data in the data area
-Layout1: or putting each date in the data area
The pivot should look like:

Item: (All) <-- drop down

Source 7/16/2004 7/23/2004 7/30/2004
Actual 400 200 300
Forecast 200 400 100

Finally add a Calculated Item:
Select the SOurce field in the Pivot
Right-Click, and choose Formula Calculated Item from the pop-up menu.
The Calculated Item dialog display. Enter the formula =Actual-Forecast
Click Ok

Now you should have

Item: (All) <-- drop down

Source 7/16/2004 7/23/2004 7/30/2004
Actual 400 200 300
Forecast 200 400 100
Var. 200 -200 200

Pivot Chart:
Right Click the pivot and choose PivotChart from the pop-up menu.

--
Regards,
Sébastien


"sameer27p " wrote:

Hi,
I have an excel workbook with two sheets.Both sheets have 'item number'
as a column.Both sheets contain different number of rows.I want to
write a macro for displaying the common items in both sheets on a page
(comparing both the sheets and getting the common item numbers)

I have to write a macro for generating the Waterfall chart
attached(Waterfall.doc).The 'Actual' values come from one sheet and the
'Forecast' value from the other of the same worksheet.(Item 1,Item
2......) are the common items in both the sheets.Variance
=Actual-Forecast.........

Does this actually need a macro or can be done using a simple excel
worksheet ?

)

Attachment filename: waterfall.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=620139
---
Message posted from http://www.ExcelForum.com/