Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Auditing utility accounts (with pivot table?)

    I work for a City and we bill for water sewer and garbage.

    I want to audit the accounts and make sure that the appropriate services are on each account. I have used ODBC to access the data tables and now have this data in excel. I have created a pivot table but am still no closer to easily finding accounts that are missing services.

    For example; lets say all accounts on Main Street should have water, sewer and garbage. I want to find out if there are any accounts that might be missing one of these services. Each service is assigned a number; water is 1, sewer is 2 and garbage is 3. How do I find the accounts on Main Street that are missing a service or are otherwise unique without looking at each record? In the example below you can see that 125 Main Street is missing sewer. We have thousands of accounts with many streets so looking at each record is time consuming. Is there an easy way to find deviations like this?

    Record Layout (3 Columns)
    123 Main Street 1
    123 Main Street 2
    123 Main Street 3
    125 Main Street 1
    125 Main Street 3
    127 Main Street 1
    127 Main Street 2
    127 Main Street 3

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,803
    Thanks
    185
    Thanked 700 Times in 638 Posts
    D.G.

    I'm a little confused?
    You say the layout is in 3 columns but your sample data shows only one or do you mean that the House number is column 1, the Street name is column 2, and the Service number is column 3? If you could post a small worksheet with fictional data it would be a big help in clarifying your setup.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Sample file attached

    I have attached a small sample file. Our actual data file has additional columns and over 18,000 lines of data. I appreciate your help on this and your past help too.

    Audit Sample.xlsx

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,803
    Thanks
    185
    Thanked 700 Times in 638 Posts
    DG,

    All you need to do is right-click on the Pivot Table header, select Show Field List, drag the Service field into the Sum Values box in the bottom right corner. Now you can compare the numbers for any that are not the same.
    ServicesPivot.JPG
    Thus 259 Elm is different than the rest and 123 Mani has less service as does 133 Maple.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    New Lounger
    Join Date
    Sep 2012
    Posts
    16
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I discovered that too but it still requires a manual search of 4,000 accounts. Can you think of way to automate a search for the variances? Eventually I will have grouped by neighborhood but those assignments are not in the system yet. The only thing that comes to mind are some if statements using street names and <> assignments for each street.
    Last edited by DGStewart; 2012-12-13 at 11:18.

  7. #6
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Ulaan Baator
    Posts
    504
    Thanks
    1
    Thanked 41 Times in 39 Posts
    I have attached a "quick and dirty" example of how you might set about this - I think that a Pivot Table may not be the best way.

    On your Data sheet I have addded a few columns, some formulas and conditional formatting.

    To make the formulas easier to write and read, I have used Named Ranges for your Address and Service Status columns. You will have to extend these ranges to encompass all your rows.

    The result is that any address which does not have all four services "Active" is highlighted in yellow. The services that each address has "Active" are noted in the columns headed with each service's name, and any missing services also show up there in yellow.

    You can copy the formulas down for as many rows as as you want, but don't forget to extend the Named Ranges too.

    I expect you can adapt this for what you want, but post back if you need any assistance.

    PS I have not gone to the effort of excluding duplicates from the reporting of missing services. It can be done, but I am not sure the effort is worth the result !
    Attached Files Attached Files
    Last edited by MartinM; 2012-12-13 at 11:23.

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,803
    Thanks
    185
    Thanked 700 Times in 638 Posts
    D.G.

    If you use Dynamic Range Names when implementing Martin's solution you won't have to worry about extending ranges when data is added.

    Ex: =OFFSET(Data!$A$5,0,0,COUNTA(Data!$A$5:$A$6005),1)
    If you define the name HouseNo with the formula above it will start at col A5 (as in your example) and extend for 6000 rows max but automatically adjust for only rows actually used.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •