sorting by Contract #'s

[Eric]

Hello,
I am needing help from the excel Masters. I am completely lost and have
tried to find my way for the past 3 days. I have given up. Please help me.

If I sort from Column A to column H this macro works great

Dim sheet1 As Worksheet
Dim myrange As Range
Dim lrow As Long
Set sheet1 = ActiveSheet
Set myrange = sheet1.Range("A11")
lrow = sheet1.Cells(sheet1.Rows.count, myrange.Column).End(xlUp).Row

Set myrange = myrange.Resize(lrow - myrange.Row + 1, 6)

myrange.sort key1:=Range("A12"), Order1:=xlAscending, _
Key2:=Range("B12"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlSortNormal

Now I need to reverse this whole thing and place the contract #'s in column
H instead of A and the above macro doesn't work. What do I need to do.

Next, I need to sort all of this info by date or by contract #. So, I need
a macro to ask me what contract number to be first so that my program will
graph it.
ie:
123
354
456

Now I want Contract 456 on top.
456
123
354

But now I want Contract 354 on top.

354
123
456

I am sorry to make this so difficult. Hope some on can help me. My email
is (e-mail address removed)

Eric Dickson

 

[Dave Peterson]

I don't think you're going to get excel to sort numbers using your custom sort
criteria. Sorting numbers is gonna be ascending or descending.

Maybe you could add another field that returns a number for each of the
contracts so you could sort by that field.

 

[Eric]

Dave,

What if I use a list box and ask for a certain contract #. Also, What about
the first part of my problem?

Eric

 

[Dave Peterson]

I don't think you're ever going to be able to sort numbers into the order you
want using plain old excel's dialogs. Maybe a helper column. Maybe changing
the data.

Maybe something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngToSort As Range
Dim LastRow As Long
Dim StartCell As Range

Set wks = ActiveSheet
With wks
Set StartCell = .Range("H11")
LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row

Set RngToSort = .Range("A11:H" & LastRow)

With RngToSort
.Cells.Sort Key1:=.Columns(8), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End With

End Sub

I figure you know if the data has headers. Change xlyes to xlno if there are no
headers--but why take a chance that excel will guess incorrectly.

I used column H to find the last used row--is that ok?

And I don't how many columns are in the range to be sorted. I use A:H.

And do you still want to sort by the second column in the range (as the
secondary key)?

 

[Eric]

Dave,

Thank you so much for your help I will be trying this on Friday morning.
You asked if the second column was going to be the second key and the answer
is yes.

In sorting the data by contract number you said that I may need to use a
helper column, what exactly do you mean? What kind of information would be
in that helper column. It can't be a number correct, because if it were we
would be back to the same problem as before sorting in ascending or decending
order. So what should that column be? Each contract will have a slight
variasion of each other so how will know what to put in that helper column or
will I have to go in an manually enter something?
IE:
Contract # Helper Column (town)
456 manchester
234 Windsor
123 Mystic

Would I know sort according to Town?
If I do this will each time I enter 456 will Manchester automaticlly be
put in to the Town column?


ERic

 

[Dave Peterson]

First, when I use the word Sort, I want an alpha-numeric sort. I don't
understand why you'd want to sort a field to put a certain value on the top and
then sort again to put another value at the top of the sorted list.

You could define a custom list (tools|Options|custom lists) and excel will sort
your data according to that list.

But if you want to rearrange values, you'll have to delete that list and
recreate a different custom list.

My thought was that you could create a table on another sheet (say sheet2) and
build a table that defines how you want your data sorted.

Contract Sortorder1 Sortorder2 sortorder3 sortorder4
1234 4 1 1 4
1235 1 2 3 3
1236 2 4 4 1
1237 3 3 2 2

Then you could use a helper column and put a formula like:
=vlookup(a2,sheet2!a:e,2,false)
and copy down.
Then sort your data using that column.

If you needed to sort in a different order, you could change the =vlookup()
formula to return a different column.

Or you could use lots of helper columns and return every conceivable order you
want and then sort your data by whatever column you want.

But I'm still confused.

Dave,

Thank you so much for your help I will be trying this on Friday morning.
You asked if the second column was going to be the second key and the answer
is yes.

In sorting the data by contract number you said that I may need to use a
helper column, what exactly do you mean? What kind of information would be
in that helper column. It can't be a number correct, because if it were we
would be back to the same problem as before sorting in ascending or decending
order. So what should that column be? Each contract will have a slight
variasion of each other so how will know what to put in that helper column or
will I have to go in an manually enter something?
IE:
Contract # Helper Column (town)
456 manchester
234 Windsor
123 Mystic

Would I know sort according to Town?
If I do this will each time I enter 456 will Manchester automaticlly be
put in to the Town column?

ERic

 

[Eric]

Dave,
I hate to have you confussed. Your probably thinking that I'm some kind of
wack job so, let me try to explain what I'm doing and trying to accomplish.

First I am in Quality Control where I design and test Asphalt (roadways). I
am making a spreadsheet that I can imput all the information from each test
right into this data base I have created. We test material each day and for
different contracts (we do alot of state work). We may do 4 or 5 tests per
day and apply it to as many as 5 state contracts. We chart individual test
results and moving average of these properties. At the end of a job we must
supply charts of each test to the state authorities. The data base places
everything in cronilogical order by date. Our charts range from January to
December and we may have 50 different contracts with 200 or more tests
involved in the charts.

I want to have the accessablitliy to chart out the properties by date (which
I can do now) or by a particular contract so that I can give them to state
authorities at the end of the job

This is how it looks now. The charts for Gmm will be according to date (7-7
through 7-9) and this chart will represent the material for 3 days and 3
different contracts.

contract # Date Gmm Gse Gsa etc.....
1. 123 7/7 2.569 2.897 2.987
2. 698 7/8 2.587 2.901 2.999
3. 123 7/8 2.569 2.994 2.999
4. 456 7/9 " " "
5. 123 7/9

I now want it to be like the following: I finished Contract 123 so I need
to print its charts with out the other contracts so.....

contract # Date Gmm Gse Gsa etc.....
1. 123 7/7 2.569 2.897 2.987
2. 123 7/8 2.569 2.994 2.999
3. 123 7/9
4. 698 7/8 2.587 2.901 2.999
5. 456 7/9 " " "

My spreadsheet only prints from #1 first. This is why I need to manipulate
the contract #'s. I hope this makes more sense. Please give me another
chance. I am desperate to get this thing working.

Eric

 

[Dave Peterson]

I'm still confused.

It looks to me like you're sorting by contract number, then by date.

If it's the printing of the data that's a problem, maybe you could determine the
unique contract numbers, then apply data|filter|autofilter to that column. Then
filter through each unique value and print those visible rows.

I still don't understand, but maybe this will help...

Maybe...

Option Explicit
Sub testme()

Dim RngToSort As Range
Dim LastRow As Long
Dim StartCell As Range
Dim myUniqueRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim ContractCol As Long

Set wks = Worksheets("sheet1")

With wks
'remove any existing autofilter
.AutoFilterMode = False

Set StartCell = .Range("H11")
LastRow = .Cells(.Rows.Count, StartCell.Column).End(xlUp).Row
ContractCol = StartCell.Column

Set RngToSort = .Range("A11:H" & LastRow)

With RngToSort
.Cells.Sort Key1:=.Columns(ContractCol), Order1:=xlAscending, _
key2:=.Columns(2), order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

'advanced filter by column H (8th column in A:H)
'to get the unique list

RngToSort.Columns(ContractCol).AdvancedFilter _
action:=xlFilterInPlace, unique:=True

If RngToSort.Columns(ContractCol).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
MsgBox "nothing in column A after the headers!"
Exit Sub
End If

With RngToSort.Columns(ContractCol)
'avoid the headers and just take the visible cells
Set myUniqueRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

For Each myCell In myUniqueRng.Cells
RngToSort.Columns(ContractCol).AutoFilter _
field:=1, Criteria1:=myCell.Value
.PrintOut preview:=True
Next myCell

'remove the autofilter from the worksheet
.AutoFilterMode = False
End With

End Sub

Earlier the contract column was column H. But in your example, it migrated back
to column A.

That makes it more confusing (to me at least).

Dave,
I hate to have you confussed. Your probably thinking that I'm some kind of
wack job so, let me try to explain what I'm doing and trying to accomplish.

First I am in Quality Control where I design and test Asphalt (roadways). I
am making a spreadsheet that I can imput all the information from each test
right into this data base I have created. We test material each day and for
different contracts (we do alot of state work). We may do 4 or 5 tests per
day and apply it to as many as 5 state contracts. We chart individual test
results and moving average of these properties. At the end of a job we must
supply charts of each test to the state authorities. The data base places
everything in cronilogical order by date. Our charts range from January to
December and we may have 50 different contracts with 200 or more tests
involved in the charts.

I want to have the accessablitliy to chart out the properties by date (which
I can do now) or by a particular contract so that I can give them to state
authorities at the end of the job

This is how it looks now. The charts for Gmm will be according to date (7-7
through 7-9) and this chart will represent the material for 3 days and 3
different contracts.

contract # Date Gmm Gse Gsa etc.....
1. 123 7/7 2.569 2.897 2.987
2. 698 7/8 2.587 2.901 2.999
3. 123 7/8 2.569 2.994 2.999
4. 456 7/9 " " "
5. 123 7/9

I now want it to be like the following: I finished Contract 123 so I need
to print its charts with out the other contracts so.....

contract # Date Gmm Gse Gsa etc.....
1. 123 7/7 2.569 2.897 2.987
2. 123 7/8 2.569 2.994 2.999
3. 123 7/9
4. 698 7/8 2.587 2.901 2.999
5. 456 7/9 " " "

My spreadsheet only prints from #1 first. This is why I need to manipulate
the contract #'s. I hope this makes more sense. Please give me another
chance. I am desperate to get this thing working.

Eric

<<snipped>>