excel hierarchical sort

Microsoft Book: office excel 2003 inside...
August 20, 2009 at 12:26:57
Specs: Windows 2000
Hi.
I have a large spreadsheet 14k rows.
I have many columns one which is 'location' and one which is 'parent'.
I'd like to sort the spreadsheet so that parent rows come before children rows, so for example the rows with parent null or blank would be first, and for any row "n", the row "p" where "p.location" = "n.parent" would be before row "n".

Is this a macro or do I need to write some jave or something. thanks
Jeff


See More: excel hierarchical sort

Report •


#1
August 20, 2009 at 19:28:49
I'm a bit confused by your question.

Perhaps if you provided some examples -before the sort and after - that might help clear things up.


Report •

#2
August 21, 2009 at 04:47:18
sure, sorry for the lack of precision.

If I have this situation to start with:

location desc parent othercolumns
buffalo small new york
rome small italy
china big asia
us big otherdata
colo med us
spain med europe
europe big
new york med us
asia big

after I run the macro on the 14k rows I'll have:

asia big
europe big
us big otherdata
colo med us
spain med europe
china big asia
new york med us
buffalo small new york
rome small italy

so if a row has a non blank parent column value, the row with that value in the location column comes before that row.

it would be nice if I could identify that things with no parents, like rome in the example actually had no parents.


Report •

#3
August 21, 2009 at 17:06:37
Sorry, but I'm still missing the logic behind the sorting.

Maybe somebody else can explain it so I can see if I can come up with a solution.


Report •

Related Solutions

#4
August 30, 2009 at 20:43:49
here is a more straight forward example

example

given

name,location,description,parent
name1,location1,description1,nullORblank
name4,location4,description4,location3
name3,location3,description3,location2
name2,location2,description2,location1

then the sort would yield:

name1,location1,description1,nullOrblank
name2,location2,description2,location1
name3,location3,description3,location2
name4,location4,description4,location3

See how it has been sorted so that if a record has a parent, say name4 with parent location3,
then it comes after the record whose location column value is location3, i.e. the parent.


Report •


Ask Question