easycapital

asked on

# 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

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

Project - Dealine type - Days before due

Thanks,

JP

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

Couldn't you just use a Pivot table for that?

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

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

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

ASKER

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

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

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

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

ASKER

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

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

ASKER

Sid,

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

Thanks,

JP

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

Thanks,

JP

So you don't want it like earlier? I mean in ascending order irrespective of Project types?

Sid

Sid

ASKER

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

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

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

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

ASKER

Can this be done with a Pivot Table?

JP

JP

ASKER

Thank you for all the input.

JP

JP

Sid