Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formulaes

Posted on 2012-03-20
5
Medium Priority
?
194 Views
Last Modified: 2012-04-03
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?
0
Comment
Question by:cmatchett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50
ID: 37744949
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
 
LVL 4

Expert Comment

by:ltsweb
ID: 37745217
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 37745225
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
 

Accepted Solution

by:
cmatchett earned 0 total points
ID: 37781055
thanks for your help on this one guys.  I ended up creating an Access database.
0
 

Author Closing Comment

by:cmatchett
ID: 37799947
i ended up using microsoft soft access
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question