Sort report by smallest to greatest - Macro (Excel 2003)

Projects are entered in a table format as shown in the embedded picture, though each column indicates how many days before different deadline within the project are due.  Looking for a way - or macro - be able to sort from smallest to greatest number of days left and obtain the following information in 3 columns:

Project - Dealine type - Days before due

Thanks,
JP
 aaM---Show-all-items-due-in-order.xls
easycapitalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
Can 2 due dates be the same?

Sid
0
DraxonicCommented:
Couldn't you just use a Pivot table for that?
0
S.A.L.FCommented:
You can sort the data in the table you have as described, due to its format. You could sort each in column or each row however the other rows or columns in the dataset would then be incorrectly ordered.

What I believe you are trying to do (and I have attached file with solution) is create a subset of the data, with the three columns you have given, and then sort them so that the shortest deadline is on top and the longest at the bottom.

I hope this is correct.
M---Show-all-items-due-in-order.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

SiddharthRoutCommented:
Since I had already started working on it. Here is a sample attached.

Let me know if this is what you want? If it is, then I will comment the code.

Sid

Code Used

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rngTable As Range
    Dim lastRow As Long, Temp As Long, MyArray() As Long
    Dim aCell As Range
    Dim i As Long, j As Long, k As Long, TotNumbers As Long, num As Long
    Dim strSearch As String
    
    Set ws = Sheets("Sheet1")
    
    Set rngTable = ws.Range("B2:D4")
    
    TotNumbers = Application.WorksheetFunction.CountA(Range("B2:D4"))
     
    ReDim MyArray(1 To TotNumbers)
    
    k = 1
    For i = 2 To 4
        For j = 2 To 4
            MyArray(k) = ws.Cells(i, j)
            k = k + 1
        Next j
    Next i
    
    num = UBound(MyArray)
    
    For i = 1 To num
        For j = i + 1 To num - 1
            If MyArray(i) > MyArray(j) Then
                Temp = MyArray(i)
                MyArray(i) = MyArray(j)
                MyArray(j) = Temp
            End If
        Next j
    Next i
    
    ws.Range("D8") = MyArray(1)
    ws.Range("D9") = MyArray(2)
    ws.Range("D10") = MyArray(3)
    
    For i = 8 To 10
        strSearch = ws.Range("D" & i).Value
        
        Set aCell = rngTable.Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If Not aCell Is Nothing Then
            ws.Range("B" & i) = ws.Range("A" & aCell.Row)
            ws.Range("C" & i) = ws.Cells(1, aCell.Column)
        End If
    Next
End Sub

Open in new window

Sample.xls
0
easycapitalAuthor Commented:
Hi Sid,

Please review the figures that I used in the attachment.  The results should be 1, 2.0, 2.5 and then bring the respective column and row header.

Also, could you make the number of rows be the same as the number of cells that represent the days due.  In other words, if there are 9 or 12 cells indicating number of days remaining then, let that be the number of rows.  I could create a
aaa.jpg
0
easycapitalAuthor Commented:
... The EE system seems to be doing some maintenance on the site, so I was not able to embed the excel file in my previous post.  

I named a range "days_range" to indicate the cells that show the days remaining.  As my report with reports will grow weekly, I need the columns to add more and more rows depending on how many day remaining cells I have.  

@ Funing:
I did not see any macros in your solutions.

@ Draxonic:
I do not think that I can create a pivot table from the report layout.  One must create columns, and the creation of the column structure is what I asking how to do via a macro.

Thanks,
Juan
Sample-jp.xls
0
SiddharthRoutCommented:
Sorry, I didn't think there would be decimals. Just change the line 6

Dim lastRow As Long, Temp As Long, MyArray() As Long

to

Dim lastRow As Long, Temp As Long, MyArray() As Double

>>>Also, could you make the number of rows be the same as the number of cells that represent the days due.  In other words, if there are 9 or 12 cells indicating number of days remaining then, let that be the number of rows.

Can you give a sample data so that I can make changes to that?

Sid
0
easycapitalAuthor Commented:
Sid,

Sid, the are in grey above can change in number of cells.      
I am thinking that the logic of the macro could be:      
      Get Project A, Deadline type A, And days in the first row.
      Continue then with the Project A, Type unitl D, then start with project B.
      Add this to each row, and add as many rows as necessary.
Then simply sort in ascending by Days before due once all the rows have been added.      
*** I created 3 named ranges Project_names, deadline_types, days_range      

Please find the attached file.

Thanks,
Juan
Sample-jp-ver-1.xls
0
easycapitalAuthor Commented:
Sid,

I will be adding more projects, so that range will grow.

Thanks,
JP
0
SiddharthRoutCommented:
So you don't want it like earlier? I mean in ascending order irrespective of Project types?

Sid
0
easycapitalAuthor Commented:
Hi Sid,

I just tried it again, and it sorts it perfectly - I thought I found it not working as expect, but it works perfect.

I wondering thought how to get the result from the macro to do it for all the days, and could the result grow if more projects are added?

Thanks,
JP
0
SiddharthRoutCommented:
>>>I wondering thought how to get the result from the macro to do it for all the days, and could the result grow if more projects are added?

Yes it is simply :)

i have hardcoded the values in the code, you simply need to change that. For example, I'll refer to the code that I gave in ID: 35239762

See the line

Set rngTable = ws.Range("B2:D4")

You can amend that to include more rows or columns :)

Sid
0
easycapitalAuthor Commented:
Can this be done with a Pivot Table?

JP
0
easycapitalAuthor Commented:
Thank you for all the input.
JP
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.