The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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

M---Show-all-items-due-in-order.xls

Project - Dealine type - Days before due

Thanks,

JP

M---Show-all-items-due-in-order.xls

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

Sid

```
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
```

Sample.xls
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

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

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

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

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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