Excel formulaes

I require to be able to count the number of times a GPO policy setting has been set across multiple GPOs.  I had this initial idea...My columns would be

COLUMN TITLE
A             SCOPE
B             POLICY SETTING NAME  
C             POLICY SETTING VALUE
D             POLICY SETTING PATH
E             GPO

I was going to do a =countif(B4:B70,"[POLICY NAME]

That would count the amount of times a policy setting has been set across all my GPOs.  I would then like it to explicitly list for me the GPOs in which the setting appears on a different sheet?
cmatchettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you could use the autofilter to filter the table by the value you want to see. Or, using Advanced Filter you could create a new table (albeit on the same sheet, but with a little VBA that can be moved to a different sheet).

cheers, teylyn
0
ltswebCommented:
You should use a Pivot Table.  You can pivot on Column E and then do a count everytime it appears.  When you click on the count, Excel will create a table of just the GPO with all of the fields you have in your original table.

If you want to provide a sample Excel sheet, I will add the Pivot.

If you want to try it yourself, highlight the table, then Insert, Pivot Table.  Verify you have the correct range.

In the Pivot Table Field List, pull down the GPO column (or whichever value you wish to isolate) into Row Labels and pull it down again into the Values box.  Make sure it says "Count"

It will build a table and then you can click on the count value to display all the GPO entries that match.

Hope it works!
0
barry houdiniCommented:
Assuming your data is in a sheet called Data then in your summary sheet assume you have the name in A1 and the COUNTIF formula in A2 then in A4 down to list the GPOs try

=IF(ROWS(A$4:A4)>A$2,"",INDEX(Data!E$4:E$70,MATCH(1,INDEX((Data!B$4:B$70=A$1)*(COUNTIF(A$3:A3,Data!E$4:E$70)=0),0),0)))

copy that down, when GPOs are exhausted for that name you get blanks

I'm assuming that all GPOs are different

regards, barry
0
cmatchettAuthor Commented:
thanks for your help on this one guys.  I ended up creating an Access database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmatchettAuthor Commented:
i ended up using microsoft soft access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.