Excel Table Sort - Single Column based on 'implied' hierarchy

[jmarkus]

Hi,

I have generated a table with a column that shows the hierarchy of part numbers. When I try to sort the column, it doesn't sort the way I would expect. I have delimiters ('>') to visually show the precedence and if I make a similar column with each field in a logical/same size way, I can get it to sort how I want. Is there a way (either using formulae or VBA code) to get the result that I want shown by the A,B,C example, but using the arbitrary part numbers? I've attached an image showing the correct sort at the top and the wrong sort at the bottom when I simply use the autofilter A->Z/Z->A sorts.

Note, I want to keep all the data in a single column, because the depth of the hierarchy is variable and I don't want an unknown number of columns in my sheet.

Thanks,
Jeff

 

[SkipVought]

Hi,

Questions:
How do you get from column A to column B? Logic, please!
Where is a sample of data to actually work with in Excel? Data, please!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmarkus]

Skip,

It's simply a parent/child hierarchy. If I use simple representations of each leaf of the tree, then Excel knows how to sort it correctly. However, once I substitute in various strings of different numbers and letters (and lengths) it doesn't know what to do. For Example:

amh66304
->B3143104
->->11546434
->->11609592
->->amw06151

is one branch of the hierarchy.

amh66304
->B3110176
->->11609592
->->12698-0012

is another, but if you look at the table sorted at the top, it is sorted by the intended parent/child hierarchy, because I am using the symbolic, same length, simple keys. However, when I sort the same table at the bottom, by the actual data, it seems to be more concerned with the length of the string, than the A>Z,1>3,i>iii sort order.

Thanks,
Jeff

 

[SkipVought]

Okay.

You have a table somewhere else that shows parent > child and depending which parent you begin with, you will generate a multi-level bill-of-material, indented or not. I have expanded many a multi-level BOM via recursive VBA.

So what does sorting have to do with a BOM? I've NEVER tried to sort a BOM. To me, that's akin to coloring an idea curley--a basic category error.

So I must be missing something.

What we've got here is... failure to communicate

We still don't have source data to work with, despite the uncertainty of understanding what it is you're trying to accomplish.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Why not this...?

[pre]
Seq Assy Comp1PN Comp2PN
1 amh66304
2 amh66304 B3143104
3 amh66304 B3143104 11546434
4 amh66304 B3143104 11609592
5 amh66304 B3143104 amw06151
6 amh66304 B3110176
7 amh66304 B3110176 11609592
8 amh66304 B3110176 12698-0012
[/pre]
...using the 2 examples you posted where the BOM levels are put into separate columns (or not) and the sorting (well it's already "sorted" isn't it?) is done by Seq.

BTW, this is how I'd expect the BOM data to be stored at a minimum...
[pre]
PN ComPN
amh66304 B3143104
amh66304 B3110176
B3143104 11546434
B3143104 11609592
B3143104 amw06151
B3110176 11609592
B3110176 12698-0012
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jmarkus]

Hi Skip,

In my worksheet, I have column A of the table at the top of my post (I used the column B to test out and show what I want/expect but it doesn't exist on my sheet). As I mentioned, I don't want to process it into separate columns because the depth of the hierarchy is variable and I don't know how many columns will be required (which will throw off other columns in my worksheet). What I'm trying to understand is the logic behind why Excel sorts the first column one way and the second column a different way. My understanding is that it is due to the length of the string in the column, otherwise why would:

amh66304>B3143104>11546434 be higher up in the sort, above other items and
amh66304>B3143104 be at the bottom of the sort

Shouldn't the first line be above the second line? Shouldn't Excel be sorting alphanumerically from left to right? And don't blanks come before other characters?

I can't wrap my head around it...

Thanks,
Jeff

 

[jmarkus]

Arrggghhh...it turns out there were extra spaces in the string that I couldn't see until I switched to a monospaced font!

Once I accounted for the extra spaces, it made sense!

Sigh...

Thanks anyways.
Jeff

 

[SkipVought]

SPACES, one reason why posting usable DATA is more advantageous than pretty PICTURES.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein