Copy number of distinct values and their details from one sheet to another.

Hi All,

I am bit new to excel programming and Stuff. I am facing some issue in a Task Can you please help

Data:

There are two sheets : Sheet1 is Input and Sheet 2 is Output.

Sheet 1 has following columns.

Item Shop Buyer Quantity in Jan Quantity in Feb

In my sheet if I filter by a Shop for example "Shop 1" . I get the following data

Shop Vehicle Buyer Quantity in Jan Quantity in Feb
Shop1 Car Jim 1 5 5
Shop1 Car Nick 5 6 9
Shop1 Scooter Jim 7 8 0
Shop1 Cycle Mary 9 0 0

Now I need the following :

1.Number of distinct vehicles purchased from "Shop 1" and place it in the Sheet "Distinct".
2.Sum of "Quantity in Jan" in sheet "Input" for all vehicles purchased from Shop1 and place in sheet distinct.

Following is the example values in Sheet "Distinct"


Shop Number of Distinct Vehicles Total Quantity in Jan
Shop1 3 22
Shop2
Shop3

Also Attached is the sheet.

Thanks.

Thanks for the reply oeldere,

your approach is very good, but here it is slightly deviating from my requirement. Can I please have data in Sheet "Distinct"> In the format specified below . Also Can I have a solution for this without the use of macros.? (If we have to use macro it will be fine but I need data in the format specified.)



Shop Number of Distinct Vehicles Total Quantity in Jan
Shop1 3 22
Shop2
Shop3


Thanks in Advance.