×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

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.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

Why not this...?

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
 
...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...
PN      	ComPN
amh66304	B3143104
amh66304	B3110176
B3143104	11546434
B3143104	11609592
B3143104	amw06151
B3110176	11609592
B3110176	12698-0012
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

(OP)
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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close