Sum of filtered results

[vilas desai]

Dear Sirs,

Wsh Name: MasterList : In col $P5:$P I have values in the format NUMBER & OR (LA,LB,LC,RA,RB,RC) EX: 43LA or 27RC etc. In Col W, I have values from a drop down list which are AI, RTD, TC, SG, HSC, DI, AO, DO, RO and PWM.
I have the following cells in the worksheet named DCS Config

D6 E6 F6 G6 H6 I6 J6 K6 L6 M6
AI RTD TC SG HSC DI AO DO RP PWM


I am looking at a formula / code to achieve this: Please see the worksheet DCS Config.When a button placed in the wsh "DCS Config" is clicked,
- First clear all contents from Row $D7 to end of rows
- Arrange the values of MasterList $P5 in ascending order in col $C7.
- Identify how many times the values in the drop down list has appeared and write this number in the appropriate cells in the worksheet DCS Config.

Next, in wsh named IO_List, I would like to have the data from MasterList sorted as shown on click of a button placed in this wsh (The button as always, will first clear the contents , without clearing the headers) Sorting would be first done on Col P then on Col W.

Thanks in advance and best regards
Vilas Desai

[HansV]

You can use a pivot table for the first, and advanced filter for the second. See the attached version.

TEST1.xlsm

[vilas desai]

Dear Hans,
Unfortunately (and fortunately too)both - Pivot Table and Advanced Filters are new to me. I tried integrating your sheets into my file both with no success for last few hours. Fortunately because this will let me learn a new thing.
As I ask your help here, I am also reading about Pivot tables and advanced filters.
My original file is attached in the email.
Also, I could not make use of the pivot table sample you gave me. Can I have the table in the usual Excel cells? What is need ed to do that? Please advise.
Thanks in advance and best regards
Vilas Desai

[HansV]

I have sent you a modified version by e-mail.

[vilas desai]

Dear Hans,
Thank you very much. Some question on the Pivot table.
1. Can the Pivot Table box be hidden? (I dont need it right now, though)
2. Can I fix the coloumn width.
3.Can I have all the 10 drop list elements (AI...PWM) appear in the table though some of them may have zero values.
4. Currently, the 10 Drop down element appear to be in ascending order. Is it possible to have them appear in the same order as they appear in the drop down box.?
Best Regards
Vilas Desai

[HansV]

I have sent you yet another version.

[vilas desai]

Dear Hans,

Thank you again. I apologise for not making being clear in the sense that I did like the Pivot Table but only wanted to have flexibility in changing the cell width and rearranging the order of the Drop down elements. However I am very happy with what you have offered in both the versions.
Best regards
Vilas Desai