Record removal w- multiple stipulations

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

FLOVETT

Felton
Thread Starter
Joined
Sep 17, 2007
Messages
79
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.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Good morning,
I like the challenge, reading it sounds simple so ...
I'll take a look and see what I can do for you.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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.
 

Attachments

FLOVETT

Felton
Thread Starter
Joined
Sep 17, 2007
Messages
79
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top