Sort data using VBA

[Mowgli]

Hi guys,

first off, apologies if this is in the wrong place etc as I have not posted before.
I have, with the help of the info on this forum, sorted out a sheet of data for a repeating report using Excel 2003.

Column A gives a unique project code, column B the project title, column C gives a "parent ID" which should for the most part correspond with the project codes in column A; for example the second line of data is project JA210 - Tardree Zone WM Improvements and this project falls under the "Parent" project that has a code of JI003

How can I use VBA to sort these records to reflect this? I want all of the records with JI003 in the Parent ID column to be displayed underneath the parent project, the same for JI005 etc. and do not want to loose the ordering of "non-child" projects which are currently sorted as ascending on Column A.

In the example provided I have cut off most of the information as each project has a lot of data associated with it. Im sure there is a simple solution out there, but every time I think I have gotten my head around it I run into a problem so any help would be greatly appreciated. This is the last piece missing to produce a report in a day that used to take a fortnight

[Leith Ross]

Hello Mowgli,

Are you saying that all columns from "B" and to the right are to be sorted by the Parent_id?

[Mowgli]

Hi Leith,

The idea is that the Project ID in column A is the primary sort however if a project has a Parent ID then it will be moved to below that Parent Project. I should probably have included an after example in the initial upload to show what I mean, it has been added in now in the first post.

Its really trying to sort primarily on Project_ID then secondly on Parent_ID I guess but being able to group the rows under the correct Parent Project

[Leith Ross]

Hello Mowgli,

I am still not able to picture how the end result should look. Can you post a small example of hoe the data should look after it has been sorted?

[Mowgli]

Sorry Leith,

Hopefully this shows what I meant a little more clearly. The first tab shows the data in its initial state. The second tab show the data once it has been sorted manually. For Example, in the "after" tab, row 22 is project JI003 and all the projects with a JI003 identifier in the "Parent_ID" Column are grouped together underneath it.

[laffers]

Would it no produce the same result recording a Macro of you manually sorting the data?

[Mowgli]

The problem is that each report varies in the number of records shown and the location, therefore if I sort them manually there may be 50 rows with a JI003 parent one month, but only 20 the next. I was hoping a macro would be able to identify where the row should go by its heading in the "Parent_ID" column and place it under the correct record which it identifies in the "Project_ID" column.

It may be something simple, like I said, but am very new to macros and VBA and this one has gotten me stumped a little. If you have a suggestion for even recording a macro that would take into account a differing number of records it would be much appreciated.

[Mowgli]

Anyone have any ideas or am I consigned to manual sorting?

[Mowgli]

Think I now have it logically for how to do this, all I need to do now is figure out the VBA. Thanks anyway ppl

Count number of times JI003 appears in Column C
Go to the Row showing JI003 in Column A
Move down a Row
Insert number of blank rows corresponding to first count (select same No Rows and Insert)
Go to first record showing JI003 in Column C
Select number of rows corresponding to first count
Cut rows
Scroll down to first blank cell in A Column
Paste Rows
Scroll up to first row (now blank)
Select No Rows corresponding to first count
Delete Rows to take out blank spaces
Repeat for other all other parent ID’s (7 more times)