Solved

Sort by another field.

Posted on 2011-09-22
29
170 Views
Last Modified: 2012-05-12
I am using the following code to filter and sort a data set.  There is a field column H or field 8.  In that field there can be values of 0, 1 ,2 which is a priority field.

How can this code be edited to include saying, give me all the priorities 0, 1, and 2 no matter the date in column T which is sorted by.  Would like the zero's ones and twos all at the top of the page no matter the date in column T, then put the rest of the date in that is < today.

Issue is that I need get all the priorities that are in first no matter the date in column T, and then try and sort column T, leaving the 0's 1's and 2's are at the top,  and  Everything else in the filter or sort would apply after the priorities are in place.

Can this even be done?

I am thinking that I may need to filter twice....  One that gives me all the priorties no matter the date in column T, then copy that over to a new sheet, then run the other filter/sort and append that to the first set, then count how many are in that and put that total in the distination field.

Need some guidance and help with these edits.    -R-
Sub DS_GetLateOrdersIncludingToday()

Dim rgFilter As Range, rgTarg As Range

Set rgTarg = Worksheets("DS - Summary").Range("F8")  'Record count goes here

Sheets("Format").Select
With Worksheets("Format")       'Worksheet to be filtered
    Set rgFilter = .Range("A1")     'First header label in range to be filtered

    Set rgFilter = Intersect(.UsedRange, rgFilter.Resize(1, 100).EntireColumn)   'All the rows of data

    With rgFilter
        .AutoFilter
        
        .AutoFilter Field:=9, Criteria1:="=*ADD*" _
        , Operator:=xlAnd
        
        .AutoFilter Field:=10, Criteria1:=Array( _
        "CRTD", "PCNF REL", "REL"), Operator:=xlFilterValues
        .AutoFilter Field:=20, Criteria1:="<=" & Date, Operator:=xlAnd  'Late including today

        .Sort Key1:=Range("T1"), Order1:=xlAscending, Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
'       Column 31, filter out any row that has REJ as contents in a cell.
        .AutoFilter Field:=31, Criteria1:="<>*REJ*", Operator:=xlAnd

'        If you want to dsiplay the cells that CONTAIN "REJ", Use => Criteria1:="=*REJ*"
'        If you want to dsiplay the cells that DO NOT CONTAIN "REJ", Use => Criteria1:="<>*REJ*"

    End With
End With

Sheets("Format").Select
End Sub

Open in new window

Book2.xlsx
0
Comment
Question by:RWayneH
  • 16
  • 13
29 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 36585945
Sorry but your past is not clear
You say I want to sort 0,1,2 ... regardless of the date That's fien it can be done
what I don't understand is you say after
Would like the zero's ones and twos all at the top of the page no matter the date in column T, then put the rest of the date in that is < today.
>>> Whe nyou first sorted by priority you will get rows like this
Row 1 0 ..... date
Row 2 0...... date
Row 3 0..... date
...
..
Row 129 1 .... date
Row 130 1 .... date
....
...
Row 175 2 ... date
...
Row 563 3 .... date
Row 5172 3 .... date
End of file.
What I mean is you have NO records that have no priority so we can sort the date !!!

Maybe I misunderstood what you need but did you mean you want to first sort by priority then within the same priority sort by date ?? I keep reading your post and don't understand what you need to acheive can you pls explain better ?
gowflow
0
 

Author Comment

by:RWayneH
ID: 36591050
It is a difficult one to explain...  I am handling it with two filters/sorts for now, but now I can not get the count and I have to copy each sort to another page  etc... it is a mess.

I thinking that I am going to stau with the two filter/sorts... so this changes the request a bit.  My thought was to skip a row and put the zero's and ones at the bottom of the dataset, then run the code posted above.  That way after the second set of code runs, both will be visible on the same page...  yes that is what I would like to do.  So there are two things that I need. (already have the filter/sort for the zero's and one written).

1. After the filter/sort is run that will give me my zero's and one's, how do I copy those down to the bottom of the dataset, so there is a single row separating them.  (no records needs to equal zero)  We can use the code posted to test this out.  Just to get the results to be posted underneath the dataset with one row separating them.

2. I then run a second filter/sort.  How do I get a count from that to post in my destination cell?  With that row separating the two datasets? (no records needs to equal zero)

An added note here...  if statements.
After the first filter/sort, some of those records may show up in the second filter/sort, so if any record from the first filter/sort shows in the second one, hide that row and do not count it in the total records... we do not want to double count a record.  Then pop the total records in the destination cell.

I am pretty sure, that to get the total records that I need from one filter/sort is not possible, so I need to break it up into two separate ones.

Hope this helps it make sense, and thanks for helping me with this. -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36591318
Sorry Sorry Sorry !!!
I am not asking you to give me the solution but if you want the solution TRUST that I can deliver ut to you !
So Forget about the code that exist forget about everything and PLEASE EXLAIN to me in SIMPLE plain english what you need to acheive at the end NOT HOW it need to be done just leave this for me.

As the code you posted and the file you posted has only 1 sheet and this code refer to a sheet called "Format" that does not exist in this workbook. So If I run this code it will halt right away.

So please EXPLAIN in simple english what you need to acheive from A to Z and I can assure you you will be more than satissfied with the answer !
gowflow
0
 

Author Comment

by:RWayneH
ID: 36592446
Sorry that file had a different filter/sort applied, please try the new sample file here.

In English. in column H, or field 8, there are priority codes.  When the codes is run, it ignores that column.  What I need is all the records that have a priority code of a zero or a 1 to show,  then follow thru with the rest of the filter code post above.

I really do want this in two sorts... if we can put the zero's and ones from column H copied to the bottom of the dataset, it will separate those hot orders.  I generate a rpt out of the final filter/sort so it work perfectly.  Ideally getting those to the top of the report, they would show first and on the first page.

The issue is how not to double counting orders... when to sort happen.  Zero and one's in column H could show in bother sorts.  Please see if we can satisfy that requirement of two separate sorts so they can be singled out when I turn the final sorts into a report.  (I format the page after all sorts are done and need the hot orders on top separate from the rest, not duplicating them.

Hope this make more sense... -R-
Sample2.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36592845
ok let me explain what I understood.

You want first to sort the whole worksheet by  priority reverse order like
priority
3
3
3
..
2
2
2

...
1
1
0
0
0

then at the first row encountered with priority 1 skip a blank row so you will have 2 groups
First group Priority up to 2 inclusive then a blank row then second group Priority 1 then 0.

Pls correct my understanding and advise what else you want me to do with hte code As till now you did not tell me clearly what you need to acheive !!! Shall I apply the existing macro to the first group so it filter it ? You want to apply the macro to the second group as well ? pls explain.
gowflow
0
 

Author Comment

by:RWayneH
ID: 36593233
You are very close, but the zero's are the highest priority and on top. (sorted smallest to largest).  There are two separate groups.  One is the code above, (which will be the second group and on the bottom(empty row above it)) and the other group of just zero's and one's (with the empty row referred to above under it)  

First set, filter/sort and copied above the filter area with a blank row separating them.  (only capturing zero's and ones)

0
0
0
1
1
1
[space]
then the results of the code at the begin of the thread
record 1
record 2
record 3
.
.
.  sorted by < or = to today column T or field 20
.
.

This is where it gets tricky.  We need to remove the duplicates before counting them... because a record could be in both sorts and we need a count of both sorts, less the dups, placed in the target cell.  (no records needs to equal zero.)
So if it is in the top set, (zero's and ones) and in the bottom set, hide the row in the bottom set and do not count that record twice, leaving it in the top set only.

I prefer keeping it to two filters, copying the first sort for only the zero and one records to the top of the dataset, leave an empty row, then refilter by the second set (code above)...   I believe getting rid of the duplicates and getting an accurate count to the destination cell is going to be the toughest part of this.

-R-



0
 

Author Comment

by:RWayneH
ID: 36593288
0
 

Author Comment

by:RWayneH
ID: 36593314
Sorry disregard file Sample3.  In this file Sample4, there are two sort macros.  One that gets just the zero and ones and another that get everything <= to today in ascending order.

These are the two sets.  Notice that Order # 4002202 and 4002203 are in both.

Would like the DS_Priorities on top and the other one on the bottom.  Remove any dups from the bottom one that are in the top set.  Then get the accurate count to the destination cell.

Sample4.xlsm
0
 

Author Comment

by:RWayneH
ID: 36593318
When running the macros in file (Sample4), make sure that the data filter is turned off.  It starts thinking that it is off -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36595539
Based on what you discribed I have developped a macro that you can activate in the attached file in the sheet Format there is a button Called Produce Results

Pls make sure your macro settings are set to medium and you enable macro once you launch this file. Activate the button and check the results in sheet Result.

Pls let me know if this is what you want or else pls clarify what is needed.
gowflow
Sample4.xlsm
0
 

Author Comment

by:RWayneH
ID: 36596000
YES!!!  this is exactly what I am looking for!!!!  You nailed it.

Thanks for adding the checking for the "Result" tab and creating/replacing it if necesary. (nice touch).
Do you see an issue with using this with a different sheet name?  Like DS-Results, MM-Results.. etc.?
All I have to do is replace the DS queries with the other queries that I have.

Very nicely done... thank you so much!!  -R-

0
 
LVL 29

Expert Comment

by:gowflow
ID: 36597418
Do you see an issue with using this with a different sheet name?  Like DS-Results, MM-Results.. etc.?
>>> I have 2 options for you:
1) I may add a prompt that ask you for a sheet name to save the results but the downside of this is that if you type a diffrent name it will not delete the one existing already and you may end up with scattered sheets
2) (I favor this one) I may Save the result in say 'DS-Results yymmdd hhmm' or 'MM-Results yymmdd hhmm' which ever you choose this will name the sheet + the date & time it was produced this way you will keep history and know exactly what time you ran the querry.

Pls advise ur choice/prefrence
gowflow
0
 

Author Comment

by:RWayneH
ID: 36598856
I have an issue when a search of Format, returns blank in the second set.  See image.  This happened when the first set returned 5 records and the second set did not return any records.

Can we cover these scenarios that if either set one is blank and set two is not.  Set one is not and set two is blank, or both sets return blank.  If both are blank we need to return a zero to the destination cell.

On the use in different sheets...  I was just going to try just changing the the "Result" to something different or is there another edit that would be required too.  -R-
Sample4Error.png
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36599473
what do you mean by
On the use in different sheets...  I was just going to try just changing the the "Result" to something different or is there another edit that would be required too.
>>> Not clear what you want. You want to rename the sheet after it has been created ? I proposed to you 2 solutions to which you didn't bother to comment !! pls comment and on my proposal so I understand what you want.

In respect to first and second set beeing possibly blank I will adjust the routine to cater for all possibilities.

gowflow
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:RWayneH
ID: 36599575
Thanks for allowing for those scenarios being blank.

I was just going to copy the whole sub, change the name and then change my queries that get set 1 and set 2.
I though by changing the line
WSResults.Name = "Results"

to

WSResults.Name = "MM-Results"  

Shouldn't the procedure work the same as the original one you wrote?

I need to switch the data that is being filtered with the calls to my other filters..  I have about a dozen of them.  I believe option two is a good one, but a bit overkill.  Copying the whole procedure and changing a few names sounds to me like an easier/quicker solution.  I gave you one of them as an example to use..

Does that make more sense? -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36600798
Well let me see if I understood well.
You have several sub like DS_Priorities and DS_GetLateOrdersIncludingToday and what you want is the same results for the other Sub is that correct ? If yes then I will need to modify My Sub to cater for this situation and cater for blank and then I will give oyu clear simple easy instructions on how to implement.

First can you pls post an other workbook that will have data + the other sub your talking about so I can make sure whatever I build is taken care of ?
gowflow
0
 

Author Comment

by:RWayneH
ID: 36601378
You are correct... however, I do not want to hard code that whole thing into one procedure....  if you can write it in a way that the only edits would be sheet, name, destination cell, and set1 and set2 queries, that would be great.  There are a number of queries that run off the Dashboard tab, when you click on a number... it will show the order that it applies to.

I attached the file for you to look at.  The cells on the Dashboard that the process we have been working on will populate the "Current Past Due Orders" cell F7, would be the destination cell.  The is to use it for numerous other cell to, let F9, F11, F13 and as many others that I can adjust it for.

I prefer leaving it as flexible as possible.  This is a work-in-process.   thanks. -R-
Daily-Metrics.xlsm
0
 

Author Comment

by:RWayneH
ID: 36601398
I forgot to mention that there are a number of rpt pgs that are hidden...  I believe there is a unhidden sheet sub called "UnhideAllOrdersDueAndSummaryTabs" that will show the rest of the tabs, if you need to see them.  -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36601766
Here it is.
1) Taking care of blank Sets
=====================
Below code is the final version of Sub GetPriorityOrders that take care of blank sets regardless in FirstSet or SecondSet. to implement simply delete the presnt one you have and replace it by this one.

2) Result Sheet Naming
==================
You can modify this Sub to Name the Sheet Result the way you like by simply editing the below 3 lines of code in this same Sub
---------------------------------------------
If Wsheet.Name = "Results" Then
Sheets("Results").Delete
WSResults.Name = "Results"
----------------------------------------------

3) As far as implementing in your precedent attahced workbook
================================================
To be honest I saw several Sub and do not know how they would interact with the little info provided so I am not in a position at this stage to give you an answer. If you feel you are able to implement them on your own pls go ahead and feel free to use the provided Sub to cater for the rest.

However if you want me to assist to implement it in the whole workbook then I feel this would be going beyond the scoope of this question and as per EE rules a question should be limited to 1 issue clear and not to many. You may however post a related question to this one after closing this question if whatever provided to you was satissfactory with the new requirement for the new question and I would be glad to help you this respect. Pls do not take these comments badly or as a sign of not wanting to cooperate or seeking for points in the contrary I am more than willing to help you but if we drag into the disscussion for implementing in the workbook I am afraid we may easily get to much in details and loose whoever is following this conversation.

Appreciate all your comments and pls advise how you would like to proceed.
gowflow
Sub GetPriorityOrders()
Dim WS As Worksheet
Dim WSResults As Worksheet
Dim MaxRow As Long, MaxRowT As Long, I As Long
Dim Foundit As Boolean
Dim RngFirstSet As Range, RngSecondSet As Range

Application.ScreenUpdating = False

Set WS = ActiveSheet
On Error Resume Next
WS.ShowAllData
On Error GoTo 0
MaxRow = WS.UsedRange.Rows.Count

Foundit = False
For Each Wsheet In ThisWorkbook.Worksheets
    If Wsheet.Name = "Results" Then
        Foundit = True
    End If
Next Wsheet

If Foundit Then
    Application.DisplayAlerts = False
    Sheets("Results").Delete
    Application.DisplayAlerts = True
End If

ThisWorkbook.Worksheets.Add after:=Sheets(ThisWorkbook.Worksheets.Count)
Set WSResults = ActiveSheet
WSResults.Name = "Results"

'Create new Sheet to Store Results of first set DS Priority
DS_Priorities

WS.UsedRange.Copy WSResults.Range("A1")
WSResults.Columns.AutoFit
MaxRow = WSResults.UsedRange.Rows.Count + 2
Set RngFirstSet = WSResults.UsedRange

'Remove the Autofilter to get all data in Sheet Format
On Error Resume Next
WS.ShowAllData
On Error GoTo 0

'Run the Second Set and store results ofsecond set after first set.
DS_GetLateOrdersIncludingToday

WS.UsedRange.Copy WSResults.Range("A" & MaxRow)
WSResults.Columns.AutoFit
MaxRowT = WSResults.UsedRange.Rows.Count
Set RngSecondSet = WSResults.Range("A" & MaxRow & ":J" & MaxRowT)

'Remove Duplicates from Second Set that exist in first set
Set RngFirstSet = RngFirstSet.Resize(, 1)
For Each Cel In RngFirstSet
    If Cel <> "" Then
        Set c = RngSecondSet.Find(Cel, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            c.EntireRow.Delete
        End If
    End If
Next Cel
    
WSResults.Range("A" & MaxRow - 1).EntireRow.Delete
MaxRow = WSResults.UsedRange.Rows.Count - 1
Sheets("DS - Summary").Range("F8") = MaxRow

On Error Resume Next
WS.ShowAllData
On Error GoTo 0

Application.ScreenUpdating = True

MsgBox ("Total Priority Orders amounted to " & MaxRow & "have been processed and now showing in Sheet 'Results'.")
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 36601797
As a clarification:
In regards to point 2)
2) Result Sheet Naming
You will need to replace the "Results" by any other naming you want like "DS-Results" or "MM-Results" you change the Results that is in betweeen 2 quotes. in the 3 code lines listed below
---------------------------------------------
If Wsheet.Name = "Results" Then
Sheets("Results").Delete
WSResults.Name = "Results"
----------------------------------------------

gowflow
0
 

Author Comment

by:RWayneH
ID: 36617001
I tested the new code and it worked for two sets.  I then removed all the records that would have been in set 2, so it would be zero records or blank, and it failed.  I saved it as Sample5.  I did not test both blank, if it failed w/ set2 empty.  Try it.  see attached. -R-
Sample5.xlsm
0
 

Author Comment

by:RWayneH
ID: 36668539
I continued with my testing and found that both sets with zero worked fine and when set1 is blank, and set2 has records that is fine too.  It fails when set 2 is blank only when there are records in set1 during the Remove Duplicates process??  It appears to fail in the same place as it did before.  -R-
Sample5Error.png
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36707802
OK sorry for this inconvenience it is hopefuly fixed now. I added in this new version a prompt for you to input sheet name. Pls check it and no need to edit the routine anymore as it takes care of correct naming as you specify it.

Pls appreciate you comment on my last post re-adapting to whole workbook.
gowflow
Sub GetPriorityOrders()
Dim WS As Worksheet
Dim WSResults As Worksheet
Dim MaxRow As Long, MaxRowT As Long, I As Long
Dim Foundit As Boolean
Dim RngFirstSet As Range, RngSecondSet As Range

Application.ScreenUpdating = False

Do
Res = InputBox("Please enter Sheet Name to save Results. If Sheet already exist it will be deleted and new values will replace old ones." & Chr(10) & Chr(10) _
        & "PS following charachters cannot be used in a sheet name /\?*:", "Results Sheet Name", "Results")
Loop Until InStr(1, Res, "/\*?:") = 0

Set WS = ActiveSheet
On Error Resume Next
WS.ShowAllData
On Error GoTo 0
MaxRow = WS.UsedRange.Rows.Count

Foundit = False
For Each Wsheet In ThisWorkbook.Worksheets
    If Wsheet.Name = Res Then
        Foundit = True
    End If
Next Wsheet

If Foundit Then
    Application.DisplayAlerts = False
    Sheets(Res).Delete
    Application.DisplayAlerts = True
End If

ThisWorkbook.Worksheets.Add after:=Sheets(ThisWorkbook.Worksheets.Count)
Set WSResults = ActiveSheet
WSResults.Name = Res

'Create new Sheet to Store Results of first set DS Priority
DS_Priorities

WS.UsedRange.Copy WSResults.Range("A1")
WSResults.Columns.AutoFit
MaxRow = WSResults.UsedRange.Rows.Count + 2
Set RngFirstSet = WSResults.UsedRange

'Remove the Autofilter to get all data in Sheet Format
On Error Resume Next
WS.ShowAllData
On Error GoTo 0

'Run the Second Set and store results ofsecond set after first set.
DS_GetLateOrdersIncludingToday

WS.UsedRange.Copy WSResults.Range("A" & MaxRow)
WSResults.Columns.AutoFit
MaxRowT = WSResults.UsedRange.Rows.Count
Set RngSecondSet = WSResults.Range("A" & MaxRow & ":J" & MaxRowT + 1)

'Remove Duplicates from Second Set that exist in first set
Set RngFirstSet = RngFirstSet.Resize(, 1)
For Each Cel In RngFirstSet
    If Cel <> "" Then
        Set c = RngSecondSet.Find(Cel, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            c.EntireRow.Delete
        End If
    End If
Next Cel
    
WSResults.Range("A" & MaxRow - 1).EntireRow.Delete
MaxRow = WSResults.UsedRange.Rows.Count - 1
Sheets("DS - Summary").Range("F8") = MaxRow

On Error Resume Next
WS.ShowAllData
On Error GoTo 0

Application.ScreenUpdating = True

MsgBox ("Total Priority Orders amounted to " & MaxRow & "have been processed and now showing in Sheet 'Results'.")
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 36708674
Thanks the new code did not fail in my tests... and the Remove Duplicates.. looked exactly the same?  The InputBox is a cool addition but I am going to need to hard code the tab names.  They are called on in other code to hide/unhide etc. and one typo and those are broke too.

I am comfortable with edits to the three lines of code, and replacing my calls to the other filters.  I will handle implementing the procedures into the workbook.  The fourth edit would be to redirect the destination cell, which will not be a problem either.  I agree that implementing into the whole workbook was not part of the original request.

If you could please remove the input box, that would be great and this will be done.  Not sure what the fix was to correct the dups error, because that looked exactly the same, but did not fail on the one w/ the input box?

Thanks for all your help with this...  it was not an easy one.  As long as we can get past the remove dups error in the second attempt, we are good.  -R-
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 36709296
If you could please remove the input box, that would be great and this will be done.  Not sure what the fix was to correct the dups error, because that looked exactly the same, but did not fail on the one w/ the input box?
>>> Well seems your into programming which I cannot guess as some askers don't even know how to goto VBA ! anyway its a small change instead of this line previously
Set RngSecondSet = WSResults.Range("A" & MaxRow & ":J" & MaxRowT)
it has been replaced by this one
Set RngSecondSet = WSResults.Range("A" & MaxRow & ":J" & MaxRowT + 1)

Just to make sure RngSecondSet will never get blank ! :) small twist.
I have grouped the "Results" into Res variable so if you need to change the sheet name then only change it at this location:

'Result Sheet Name and Destination Cell
Res = "Results"
Dest = "F8"
change the value of the variable Res to be what you want and it should do hte trick. and set the destination cell where you want the result to appear as value for Dest and also it should do the trick.

I could automate all this very nicely for you but seems your not eager to learn !!! :) In 2 words you would have 1 routine with all the multiple calls that you want to cater for your whole workbook !!!

Good luck for implementing the whole workbook, however if you have problems and need to post a question so I can help you post it as related to this one this way I get notified and can action it rightaway.

Here is the new Sub without the Inputbox
gowflow
Sub GetPriorityOrders()
Dim WS As Worksheet
Dim WSResults As Worksheet
Dim MaxRow As Long, MaxRowT As Long, I As Long
Dim Foundit As Boolean
Dim RngFirstSet As Range, RngSecondSet As Range
Dim Res As String, Dest As String

Application.ScreenUpdating = False

'Result Sheet Name and Destination Cell
Res = "Results"
Dest = "F8"

Set WS = ActiveSheet
On Error Resume Next
WS.ShowAllData
On Error GoTo 0
MaxRow = WS.UsedRange.Rows.Count

Foundit = False
For Each Wsheet In ThisWorkbook.Worksheets
    If Wsheet.Name = Res Then
        Foundit = True
    End If
Next Wsheet

If Foundit Then
    Application.DisplayAlerts = False
    Sheets(Res).Delete
    Application.DisplayAlerts = True
End If

ThisWorkbook.Worksheets.Add after:=Sheets(ThisWorkbook.Worksheets.Count)
Set WSResults = ActiveSheet

WSResults.Name = Res

'Create new Sheet to Store Results of first set DS Priority
DS_Priorities

WS.UsedRange.Copy WSResults.Range("A1")
WSResults.Columns.AutoFit
MaxRow = WSResults.UsedRange.Rows.Count + 2
Set RngFirstSet = WSResults.UsedRange

'Remove the Autofilter to get all data in Sheet Format
On Error Resume Next
WS.ShowAllData
On Error GoTo 0

'Run the Second Set and store results ofsecond set after first set.
DS_GetLateOrdersIncludingToday

WS.UsedRange.Copy WSResults.Range("A" & MaxRow)
WSResults.Columns.AutoFit
MaxRowT = WSResults.UsedRange.Rows.Count
Set RngSecondSet = WSResults.Range("A" & MaxRow & ":J" & MaxRowT + 1)

'Remove Duplicates from Second Set that exist in first set
Set RngFirstSet = RngFirstSet.Resize(, 1)
For Each Cel In RngFirstSet
    If Cel <> "" Then
        Set c = RngSecondSet.Find(Cel, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            c.EntireRow.Delete
        End If
    End If
Next Cel
    
WSResults.Range("A" & MaxRow - 1).EntireRow.Delete
MaxRow = WSResults.UsedRange.Rows.Count - 1
Sheets("DS - Summary").Range(Dest) = MaxRow

On Error Resume Next
WS.ShowAllData
On Error GoTo 0

Application.ScreenUpdating = True

MsgBox ("Total Priority Orders amounted to " & MaxRow & "have been processed and now showing in Sheet 'Results'.")
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 36709767
Please do not get me wrong,  I am eager to learn here...  If I am understanding this final version of the solution, all I have to chg is Ln 12 and 13 and then my call tags.  I appreciate your offer to group all the call tags together, but that is where I am learning.  I like to do them one at a time first, to get a good idea how the code works, then I will streamline it and group them all into one, as you mentioned.  True it maybe another question/thread later, but for now I am sure that I can run w/ it from here.

Thanks for the help, and such an awesome solution.  I was doubtful at first, because of my bad explaination to begin with, (shame on me).  You came through!!!

EXCELent!!  -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36710777
no problem pls make all your tests and sorry if I got u confused no problem for me.
gowflow
0
 

Author Closing Comment

by:RWayneH
ID: 36711773
EXCELent!! solution worked great and implemented into workbook.  All testing worked and COMPLETELLY satisfied with solution.  Thanks. -R-
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36716229
Tks for the grade and gald it worked for you.
gowflow
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now