formula for getting report

[sambit]

Sir,
we have two place of sale i.e, japan and Italy. how to Prepare date wise price comparision statement where japan price is > (greter than) Italy Price.

Condition : Material Type, Material Code, Grade, Thickness, Width and Finish are same.

please find attached sale data cum report.xlsx where you get two sheet, one is sale data and another sheet is report.

my question is how to get data like report sheet ?

what formula to apply for getting report ?

please help.

 

[kchiba]

Hi Sambit,

I have included a vlookup formula in your file. Please note that the invoice numbers on the sales data tab are formatted as text and not numeric

 

[sambit]

Hi kachiba,
Thanks for reply. But you applied the formula for getting price coloum. but I want how to retrieve entire report from sales data.

 

[Somendra Misra]

Hi,

See the file, Pivot Sheet, to see the pivot report.

Regards,

 

[kchiba]

Hi,

See attached pivot file

 

[sambit]

Hi Somendra,
Thanks for reply. I have gone through Pivot table. From pivot table, I have to do manually indentify. i.e, each line item to be checked. which is not easy task.

 

[sambit]

Hi kachiba,
Thanks for reply. I have checked Pivot table and found manually indentify the line item.

 

[Somendra Misra]

@sambit

Why have you highlighted those rows in PT? Do you just want the output as you mentioned in your sample file and if so, than what is the criteria ?

Regards,

 

[sambit]

Hi somendra,
Many many thanks, you understood my requirement.
I want date wise price comparision report. where Material Code, Type, Grade, Thickness, Width, Finish are same. if you scrutinize the sale data you get On Date 14.08.2012 Price of Italy Rs. 96,120 and price of Japan is Rs. 96,820. Where Japan price is > Italy price.

 

[sambit]

waiting for reply......

 

[Somendra Misra]

@sambit ,

Will you select date also?

Regards,

 

[sambit]

yes

 

[Somendra Misra]

Will partial VBA will be OK?

Regards,

 

[sambit]

somendra sir,
if you think that it is possible through VBA, then you may proceed.

 

[Somendra Misra]

@sambit

Can you wait till tomorrow morning, I will create something today.

Regards,

 

[sambit]

OK

 

[Somendra Misra]

Hi Sambit,

See the file. It uses advanced filter through VBA to extract data on ReportSM sheet and than uses Pivot table to generate the report.

I had use a helper sheet List to list all the drop down values and than used named dynamic ranges to get the list.

See if this helps you.

Regards,