Sorting rows with alphanumeric starter cells

[axlmastr]

I have a worksheet with rows of information. The start cell of each row
(in column A) has a part number which may or may not contain a suffix.
The suffix is usually of the format "part#_1" or "part#AT" for example.
In other words the same part number maybe repeated and I distinguish
the repeats with an alpha suffix or underscore and numeral. The
problem I'm having is sorting the sheet by column A, part#, and those
part numbers with their suffixes. The suffixes cause the sort to list
the all "number only" part# to be at the top followed by a separate
list of the suffixed numbers in numerical order at the end. You have
to remember to scroll to the end portion of the listing to see if their
are any additonal part number variables to the originating one since the
rest of the data in those rows may contain different information. How do
I get the numbers to sort properly with the suffixed following their
respective parent part#?

 

[RagDyeR]

Is there a space between the alpha *prefix* and the actual part number
*suffix*?

If there *IS*, you can try using Data - Text To Columns to create a "helper"
column, and then sort on the helper.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message
I have a worksheet with rows of information. The start cell of each row
(in column A) has a part number which may or may not contain a suffix.
The suffix is usually of the format "part#_1" or "part#AT" for example.
In other words the same part number maybe repeated and I distinguish
the repeats with an alpha suffix or underscore and numeral. The
problem I'm having is sorting the sheet by column A, part#, and those
part numbers with their suffixes. The suffixes cause the sort to list
the all "number only" part# to be at the top followed by a separate
list of the suffixed numbers in numerical order at the end. You have
to remember to scroll to the end portion of the listing to see if their
are any additonal part number variables to the originating one since the
rest of the data in those rows may contain different information. How do
I get the numbers to sort properly with the suffixed following their
respective parent part#?

 

[axlmastr]

@RagDyeR

Here's and example of my list in column A:


7213
7214
7323AT
7323MT
7333AT
7333MT
7401
7401_2
7401_3
7402
7402_2

I want to sort the rows by the column and have the order you see above.
I want the mixed use numbers (with suffixes) to be in the same order as
the standard numeral type part numbers. Instead I get the mixed use
numbers listed in order at the end of the numerical list.

 

[RagDyer]

Using TTC (Text To Columns), this is the best I could do:

Is it good enough?

7213
7214
7323AT
7323MT
7333AT
7333MT
7401_2
7401_3
7401
7402_2
7402

Let me know if it's good enough and I'll walk you through it.

If it's not ... sorry!

 

[axlmastr]

Using TTC (Text To Columns), this is the best I could do:

Is it good enough?

7213
7214
7323AT
7323MT
7333AT
7333MT
7401_2
7401_3
7401
7402_2
7402

Ya it's better than I have now. I see that the underscore places
first, but that is a mild compromise for what you have acheived
otherwise. Please advise, Thanks

 

[RagDyer]

Say your column of data is Column A.
You'll need 2 empty "helper" columns adjacent to A, so make sure B and C are
empty, OR, insert 2 columns temporarily.

Select the column of data, then:
<Data> <TTC>
Clicked "Fixed Width", then <Next>

Click in the "Data Preview" window to create a "break line", and drag it to
separate the 4 digits from the rest.
Then <Next>

You now see how TTC will separate the columns.

The default location in the "Destination" window is your original column of
data.
This means TTC will *replace* your original data with the revised, separated
data.
You *don't* want this, so change the location in the destination window to
B1.
This tells TTC to retain the original column and start entering the revised
data into Column B.
Now, click <Finish>

You now have Columns A, B, and C, where A is the original data, B is the 4
digits, which are now *true numbers*, so they'll sort correctly as you want,
and C, which is text.

Select *all* 3 columns and
<Data> <Sort>
Set first sort key to Column B, ascending, and the second to Column C,
ascending, then <OK>.

You should now have what I showed you.

You can, of course, now delete the "helper" columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[axlmastr]

@RagDyer
Thanks for the influencial info. I have just produced a test run o
you instructions line for line. I am at a slight impass. I need t
mention that I have data in cells across each row that one of thes
part numbers represents. Basically, to save myself writing a databas
in Access, I use the worksheet as a Bill Of Material. I hav
structured column headings from A-M. Each part number has it's ow
row, except for those that repeat due to their difference i
components. The easiest way I find to distinguish items with the sam
Part Numbers but different components is by codes I add as suffixes.
can sort the three columns using you method, though I still see the tru
numbers in column B with the text in column C. How do I delete th
helper cells (as you suggest) and not lose the data (true number
and/or text) in them and still retain my original starter column wit
respect to the resulting sort? Also how do I include the remainin
columns in the sort? Until now I selected the whole sheet and sorte
by Column A and it worked ok, but I really like the results you showe
me and would rather use your method if possible. Or can you tell me
better way to enter the part numbers in order to sort more cleanly?
Thanks RagDye

 

[Ragdyer]

If I understand what you're saying, you've *inserted* a new Column B and C
to accept the parsed, separated data from Column A.

So now, just do what you previously did, and select your "whole" sheet, and
instead of sorting on Column A, just sort on Column B & C, and all the
associated data in the adjoining columns will follow.

And, there's really nothing that says that you must delete the new B & C.

 

[refinate]

axlmastr,
The Excel addin "Refinate" will do the kinds of things
with BOM that you want to do and much more. It will save
you lots of time as you get everything done just by
clicking the mouse.

It is designed specifically to work with bill of materials
and assembly lists. It also processes net lists so that
you can verify design changes and compare differences to
older versions of the netlist.

To sort the way you describe, drag to select all of your table and use
Refinate's "one-row-per-part". This automatically converts the part# to
text and they will sort by text rules. A bonus result of this process
is that table formatting is applied and much of your print page setup
is done automatically.


www.refinate.com


--Brian Taylor
AnalogDigital Engineering