Sorting But Maintaining Row Relationships

[John13]

I have a large database in Excel where the Project name is on one row
and the sub tasks are listed on the rows below it. I want to sort by
Priority (all 1's, then all 2's etc.) but don't want to loose the
relationship to the sub-tasks listed below each. Like below, rows 2, 3
& 4 are together as is 5 & 6 and 7, 8 & 9.

Is it possible? Some of the Agenda Items (Project names) have 7 or 8
sub-tasks, some only one. The Next Action sub-tasks are not listed as
Step 1, etc. but rather their required action (for example: Download
database or order samples for testing).

Any ideas? Thank you for your time.

John


A B C D
E F G
1 Priority Agenda Item Next Action Eng Mkting Sales Acct.
2 1 Marketing Analysis
3 Step 1 X X
4 Step 2 X X
5 2 Design analysis
6 Step 1 X
7 1 Testing
8 Step 1 X
9 Step 2 X
10

 

[Dave Peterson]

You could add a helper column and put key for each group. This field would
would have to include the primary key and sequencing for the other rows.

Maybe something like:
priority#.Sequence#.GroupingIndicator

001.0001.MarketingAnalysis
001.0002.MarketingAnalysis
001.0003.MarketingAnalysis
001.0004.MarketingAnalysis

002.0001.DesignAnalysis
002.0002.DesignAnalysis
002.0003.DesignAnalysis

....

Then sort by that helper column.

Another option would be to put all the data on one row, then sort that data. If
you have to, you could then extract the data into a report format. (I'd still
use the one row per record for updating, though. The report format worksheet
would be for viewing/printing only.)

 

[John13]

You could add a helper column and put key for each group. This field would
would have to include the primary key and sequencing for the other rows.

Maybe something like:
priority#.Sequence#.GroupingIndicator

001.0001.MarketingAnalysis
001.0002.MarketingAnalysis
001.0003.MarketingAnalysis
001.0004.MarketingAnalysis

002.0001.DesignAnalysis
002.0002.DesignAnalysis
002.0003.DesignAnalysis

...

Then sort by that helper column.

Another option would be to put all the data on one row, then sort that data. If
you have to, you could then extract the data into a report format. (I'd still
use the one row per record for updating, though. The report format worksheet
would be for viewing/printing only.)








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Too many rows to add helper. How do you "extract data into a report
format"? That might be the solution. Thank you for your help.

John

 

[Dave Peterson]

First, I would spend time creating essentially a flat file database. Move
things up from each of the subordinate rows to the main row (to the right of the
common data).

Then after you have that, you could have a macro that creates a new worksheet,
then copies the left hand side data to the first row, then the next set to the
second row of the group, then the next set to the 3rd row of the group, ....

Just a series of copy|pastes. This is typed into the post--not compiled, not
tested--just an idea.

dim CurWks as worksheet
dim RptWks as worksheet
dim iRow as long
dim oRow as long
dim firstrow as long
dim lastrow as long
dim iCol as long

set curwks = worksheets("database")
set rptwks = worksheets.add
'add headers to rptwks here

with curwks
firstrow = 2 'headers in row 1
lastrow = .cells(.rows.count,"A").end(xlup).row

orow = 2
for irow = 2 to lastrow
.cells(irow,"A").resize(1,4).copy _
destination:=rptwks.cells(orow,"A")
orow = orow + 1
for icol = 0 to 4 'max 5 subordinate groups??
if isempty(.cells(irow, 5 + (icol * 6).value) then
'skip that section
else
.cells(irow, 5 + (icol*6).resize(1,6).copy _
destination:=rptwks.cells(orow,"E")
orow = orow + 1
end if
next icol
next irow
end with

But those resizes and increments depend on the layout of your flat database.

(And watch out, there could be math/logic/syntax errors!)