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

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

Hi All,

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.

2. ## Re: Copy number of distinct values and their details from one sheet to another.

I re-aranged the data with an macro.

After that I used pivot table to get the result.

See the attached file.

3. ## Re: Copy number of distinct values and their details from one sheet to another.

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

4. ## Re: Copy number of distinct values and their details from one sheet to another.

Maybe other members have a smart formula solution.

I would go for the given option.

5. ## Re: Copy number of distinct values and their details from one sheet to another.

Taj, try these.

Define two more names Shop & Vehicle with appropriate ranges. Then in;

Distinct A2:

=IFERROR(INDEX(Shop,MATCH(TRUE,INDEX(ISNA(MATCH(Shop,A\$1:A1,0)),),0)),"")

B2:

=IF(A2="","",SUMPRODUCT((Shop=A2)*(Vehicle<>"")*ISNUMBER(1/(MATCH(Vehicle&"_"&A2,Vehicle&"_"&Shop,0)=ROW(Shop)-MIN(ROW(Vehicle))+1))))

C2:

Copy A2:C2 then copy down.

IFERROR only works with Excel 2007 or above. A per you profile assuming you are using Excel 2010.

There are currently 1 users browsing this thread. (0 members and 1 guests)