1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Record removal w- multiple stipulations

Discussion in 'Business Applications' started by FLOVETT, Jan 20, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. FLOVETT

    FLOVETT Thread Starter

    Joined:
    Sep 17, 2007
    Messages:
    79
    First Name:
    Felton
    I have been tasked with creating graphs/charts, so I have to remove some of the records base on certain criteria. I found it best to sort the data first, then begin to remove records. This can be done manually, but it becomes cumbersome and mistake prone as the records increase. I would like some VBA code to automate this task.

    1. Remove all duplicate records
    2. Sort by Location (col A), Date of Svc (Col G), Issue Type (Col M), Service Type ( Col T)
    3. Remove second and subsequent records where Issue Type of 'None' appears on the same svc date for the same location.
    4. Remove all Records That have the same Location, Same Date of Svc if Issue Type of None appears and Other Issue Types appear on that same Svc Date

    * a location should only have 1 record if all records for that location and Issue types are 'NONE' for that service date
    (all other records for that date will be deleted)
    * a location should never have an issue type of 'None' and other issues appearing on the same date of service
    (all other issue types override 'NONE' issue type).
    * a location can have multiple issue types on the same date of service, only if they are not issue types of 'NONE'.

    I have attached an example of the DATA (Start) and (End <-- result).
    Thanks.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Good morning,
    I like the challenge, reading it sounds simple so ...
    I'll take a look and see what I can do for you.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Well the sort macro is written, that's the simple part

    I think the issue is quite simple for you all that sorting and looking nfor the same date.
    You marked all the rows for deletion that contain "none"

    I think sorting and then a macro from bottom up to delete all the rows where there is 'NONE' in issue types?

    Maybe, instead of deleting the rows, copy the data to a Table sheet which you use as data for the graph, that way the graph will always be populated depending on the data in that sheet.

    The macro name is SortStep001()


    If you have a file with the graph and you want me to show what I am trying to explain (which may sound crazy) just attach it.
     

    Attached Files:

  4. FLOVETT

    FLOVETT Thread Starter

    Joined:
    Sep 17, 2007
    Messages:
    79
    First Name:
    Felton
    Hey,
    thanks for the response, once again. I am sorry I haven't responded in a while. The beginning of the Month is always pretty hectic. The reason that the rows are deleted and not copied to another page, is that the rows with RED coloring add not value to the graphs (they will not be totaled in). I will upload an example for you tomorrow when I return to work.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Record removal multiple
  1. jamesb2
    Replies:
    1
    Views:
    260
  2. jamesb2
    Replies:
    6
    Views:
    349
  3. crashdown
    Replies:
    12
    Views:
    241
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086105

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice