MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorting troubles


Posted by George on March 05, 2001 1:31 PM

I have a worksheet w/part numbers and part names in 9 different columns, A-I.
The way I have it now, the data in columns B-I correspond to the number in Column A.
I am trying to sort the data so that I may lookup any number or name and still
have the rest of the data correspond to each other.
For example, in the data below, the number/names in column B-E correspond to the
Master Number in column A. Let's say that I wanted to sort the data by component name
(column D) or component number(column E), and still have the data in the other
columns correspond to these names/numbers. I hope this is clear enough for you.
Thanks in advance!

A B C D E
Master Number Assembly number Code Name Component Name Component #
1234 567 xyz bolt 891
nut 745
plate 637
rod 250

4321 856 abc bolt 426
nut 451
plate 637
rod 250


Posted by GregA on March 05, 2001 7:07 PM

George,

Am I correct in interpreting your example to mean that "nut 745" refers to a component called "nut", comp. # 745, whose master number, assy # and code name are 1234 567 xyz? If not, what are those items for nut 745?

Second clarification: Do you want to sort by component name and have nut 451 and nut 745 grouped together, and be able to associate the corresponding master # etc?

Thanks

Greg

Posted by George on March 06, 2001 7:55 AM


Sorry for the confusion Greg. Yes your assumptions were correct in both cases.
The master number,1234, has 4 components to it(bolt,nut,plate rod)
each having it's own part number(891,745,637,250). I would like to sort the data in different
ways. Let me clarify my previous example.
I want to be able to lookup all bolts or all nuts linked to the Master Number and
still show what master number,assy number,code name,part number that the bolt or nut
is associated with. Similarly, I would like to lookup data by Code Name or Part Number.
I hope this makes more sense Greg.

Thanks,
George


Posted by GregA on March 07, 2001 4:10 AM

George,
In order for you to be able to sort and maintain all the data relationships, you need to arrange your data table so that each row contains ALL the data elements. It may be more visually appealing to see one instance of the master number, assembly number and code number, and then all the related parts, but you can't do the sorting without that information. So . . .

First, populate your data so that all the columns are filled in for each row. This is just a copy and paste job. Now you can sort and resort your data as you like.

Second, if you want a "prettier" view of the data you have two choices: you can create a pivot table, or you can sort and format manually. Pivot tables will omit repeated values and can be set up to create a page that looks like the one you have. You can do the same thing by copying your data to a new worksheet, sorting, and then deleting repeated values to make the sheet look the way you would like.

Hope this helps.

Greg Sorry for the confusion Greg. Yes your assumptions were correct in both cases. The master number,1234, has 4 components to it(bolt,nut,plate rod) each having it's own part number(891,745,637,250). I would like to sort the data in different ways. Let me clarify my previous example. I want to be able to lookup all bolts or all nuts linked to the Master Number and still show what master number,assy number,code name,part number that the bolt or nut is associated with. Similarly, I would like to lookup data by Code Name or Part Number. I hope this makes more sense Greg. , George

Posted by George on March 07, 2001 1:40 PM

That makes sense. I'll try it out and let you know how
it turns out. Thanks for your help Greg.

George

George, In order for you to be able to sort and maintain all the data relationships, you need to arrange your data table so that each row contains ALL the data elements. It may be more visually appealing to see one instance of the master number, assembly number and code number, and then all the related parts, but you can't do the sorting without that information. So . . . : : Sorry for the confusion Greg. Yes your assumptions were correct in both cases. : The master number,1234, has 4 components to it(bolt,nut,plate rod) : each having it's own part number(891,745,637,250). I would like to sort the data in different : ways. Let me clarify my previous example. : I want to be able to lookup all bolts or all nuts linked to the Master Number and : still show what master number,assy number,code name,part number that the bolt or nut : is associated with. Similarly, I would like to lookup data by Code Name or Part Number. : I hope this makes more sense Greg. , : George

Posted by George on March 08, 2001 1:51 PM

Re: Thanks Greg

It worked!!! Thanks for your help Greg.

George