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

S

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!
 
M

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.
 
S

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
 
D

David Biddulph

This is not a binary newsgroup, so Excel attachements are not allowed here.
If you wish to, you can upload a file to a web site, and post the url here.
--
David Biddulph

....
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.
....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top