How to make list of Items displayed in AutoFilter? Any Function?

[Soham]

Hi,

Dear I am beginner in EXCEL,

Can anybody explain me, How to make list of Items displayed in AutoFilter?
Any Function?

Currently I am doing manually by each individual items using autofilter,then
Copy-paste. This consumes lot of time and hundreds of items are there. e.g.

Datasheet

Apple 1
Apple 2
Apple 9
Banana 3
Banana 4
Carrot 5
Doll 1
Doll 7
Doll 5
Doll 6


List - Summary sheet

Apple 12
Banana 7
Carrot 5
Doll 19


Please help. I have tried to know by help but not succeded.

Thanks in advance!

 

[Pete_UK]

Follow this link:

http://www.contextures.com/xladvfilter01.html#FilterUR

for a description of how to prepare the list of unique values in your
summary sheet using advanced filter. There is also a short video
there.

Once you have your list, you can use a SUMIF function in the next
column to get the numbers.

Hope this helps.

Pete

 

[Gary''s Student]

This is the perfect job for a pivot table. See:

http://www.contextures.com/xlPivot07.html#Unique

and if you are not familiar with pivot tables:

http://www.contextures.com/xlPivot01.html

 

[Soham]

Dear Pete,

Yes! It worked!

Thank uou very much!!

Soham

 

[Soham]

Thanks for the reply!

My querry solved by Pete's answer of using following link:
http://www.contextures.com/xladvfilter01.html#FilterUR

Thanks!

Soham

 

[Max]

The easier? option, imo, as per GS' response, is to create a pivot. Don't
dismiss this option out-of-hand. Here's the quick steps to lead you in. In
Layout, drag n drop the Items col header into the ROW area, the "amounts" col
header into the DATA area (it'll show as Sum in DATA), and yup, that's it.
The pivot returns exactly the results that you're after, a list of the unique
items and next to it the corresponding sums of the amounts. You're done in
5-10 seconds.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Dear Pete,

Yes! It worked!

Thank uou very much!!

Soham








- Show quoted text -

 

[Soham]

Dear Pete,

Further querry is, Can you guide me, please?

Querry pasted, I want to attach Excel sheet, but do not know, how to attach?
Can help? because pasted querry may not disply exactly as it is.


Querry:
I want to prepare a Summary based on the data given on two separate sheet as
follow:

Specification (From A on Separate Sheet)
ORIGIN Material CODE Material NAME
ITALY MF-001 FIOR CARNICO
CHINA MF-002 HONEY ONYX
ITALY MF-003 WHITE STATUARIO

Bill of Quantities (From B on Separate Sheet)
Location Item Description Material CODE Material NAME Rate Quantity Amount
Room1 Cladding A MF-001 FIOR CARNICO 1.00 2.00 2.00
Room1 Cladding A MF-002 HONEY ONYX 3.00 3.00 9.00
Room1 Cladding A MF-003 WHITE STATUARIO 5.00 5.00 25.00
Room1 Cladding B MF-001 FIOR CARNICO 2.00 4.00 8.00
Room1 Cladding B MF-002 HONEY ONYX 4.00 6.00 24.00
Room1 Cladding B MF-003 WHITE STATUARIO 6.00 2.00 12.00
Room1 Flooring A MF-001 FIOR CARNICO 7.00 4.00 28.00
Room1 Flooring A MF-002 HONEY ONYX 9.00 5.00 45.00
Room1 Flooring A MF-003 WHITE STATUARIO 11.00 2.00 22.00
Room1 Flooring B MF-001 FIOR CARNICO 8.00 1.00 8.00
Room1 Flooring B MF-002 HONEY ONYX 10.00 3.00 30.00
Room1 Flooring B MF-003 WHITE STATUARIO 12.00 5.00 60.00


Room2 Cladding A MF-001 FIOR CARNICO 1.00 8.00 8.00
Room2 Cladding A MF-002 HONEY ONYX 3.00 5.00 15.00
Room2 Cladding A MF-003 WHITE STATUARIO 5.00 4.00 20.00
Room2 Cladding B MF-001 FIOR CARNICO 2.00 1.00 2.00
Room2 Cladding B MF-002 HONEY ONYX 4.00 2.00 8.00
Room2 Cladding B MF-003 WHITE STATUARIO 6.00 3.00 18.00
Room2 Flooring A MF-001 FIOR CARNICO 7.00 5.00 35.00
Room2 Flooring A MF-002 HONEY ONYX 9.00 6.00 54.00
Room2 Flooring A MF-003 WHITE STATUARIO 11.00 4.00 44.00
Room2 Flooring B MF-001 FIOR CARNICO 8.00 5.00 40.00
Room2 Flooring B MF-002 HONEY ONYX 10.00 2.00 20.00
Room2 Flooring B MF-003 WHITE STATUARIO 12.00 3.00 36.00

Option 1 - without making Rate table as below]
Summary (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula)
Filling Manually Filling Manually Filling Manually Filling Manually Filling
Manually Formula
Item Description Material CODE Material NAME Rate Quantity Amount
Total Cladding A MF-001 FIOR CARNICO 1.00 10.00 10.00
Total Cladding A MF-002 HONEY ONYX 3.00 8.00 24.00
Total Cladding A MF-003 WHITE STATUARIO 5.00 9.00 45.00
Total Cladding B MF-001 FIOR CARNICO 2.00 5.00 10.00
Total Cladding B MF-002 HONEY ONYX 4.00 8.00 32.00
Total Cladding B MF-003 WHITE STATUARIO 6.00 5.00 30.00
Total Flooring A MF-001 FIOR CARNICO 7.00 9.00 63.00
Total Flooring A MF-002 HONEY ONYX 9.00 11.00 99.00
Total Flooring A MF-003 WHITE STATUARIO 11.00 6.00 66.00
Total Flooring B MF-001 FIOR CARNICO 8.00 6.00 48.00
Total Flooring B MF-002 HONEY ONYX 10.00 5.00 50.00
Total Flooring B MF-003 WHITE STATUARIO 12.00 8.00 96.00

Rate Table (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula) (Shown in Green Cells
Material CODE --> MF-001 MF-002 MF-003
Material NAME --> FIOR CARNICO HONEY ONYX WHITE STATUARIO
ORIGIN--> ITALY CHINA ITALY
Filling Manually Rates filling Manually Rates filling Manually Rates
filling Manually
Cladding A 1.00 3.00 5.00
Cladding B 2.00 4.00 6.00
Flooring A 7.00 9.00 11.00
Flooring B 8.00 10.00 12.00

Option 2 - with making above Rate table and using the same]
Summary (I am preparing Manually - on Separate Sheet but want to Prepare
using some function/formula)

Thanking you in advance for guidance!

Waiting for reply.

Soham

 

[Soham]

Thanks David,

Is it right, I have done as follow?

File stored in My Workspace and link is as follow
http://workspace.office.live.com/#soham1809/Documents/SPM-Room-Qtty-Fmt-Querry - Exanple.xls

Soham