Sorting a list of electronic components?

Question

In a spreadsheet the bill of materials of electronic components lists the components by their values, for example, 1R0 (for 1 Ohm), 1k0 (for 1 kilo-Ohm), or 22p (for 22 pico-Farad), 1n0 (for 1 nano-Farad). How can numbers in this format be sorted in numerical order?

Before, unsorted:

Resistors

• 1K0
• 1R0

Capacitors

• 1n0
• 22p

After, sorted:

Resistors

• 1R0
• 1K0

Capacitors

• 22p
• 1n0

Answer 1

Since the chosen set of abbreviations does not lend itself to "A-z" alphabetizing, the traditional solution would be to include one or more helper columns that hold the required information and perform the actual sort upon those columns, not the shown source column. One can also hide those helper columns, or place them far to the edge of the data to reduce their distraction.

You look to have a "used in conversation" value, like "22", say, a "kind" of value, "resistance" or "capacitance", for instance, and a "level" modifier, like "nano" perhaps. So three helper columns.

(You could type all the following as things are entered into the data, or modified, but the following isn't complicated, just tedious, and could be done later, for that matter, as an improvement added along the way.)

The first could strip out the used in conversation value using the regular list of string functions. The second might strip out the level modifier and the third the kind modifier. You'd strip those bits out with the formulas in the appropriate columns, and then act upon those results, if need be.

For instance, the "p" taken out could then be fed into a VLOOKUP in a table of modifiers and their word names. Apparently, some values talked about here would have to be slightly more creatively sussed out, but they look pretty easy to do. For the level modifier, you'd want to then multiply the used in conversation value by the appropriate 0.000001 or whatever that fits the level modifier so you have a "true" "A-z" value for the sorting. You would combine those two columns, now or later, into a bigger formula in a single column since this is the actual thing you need. With the two columns for that approach, you'd need another column to combine their results into real sortable values. So your three columns would become two or four, depending on your approach. Again though: "now or later"... you can do the easy four total columns approach now and better it into just two columns later. Whichever seems best to you.

Once you have the columns filled with formulas, you have all you need to sort upon to achieve what you want.

If the columns have formulas, you can hide them as the user hasn't much need to see them. Cleaner look and they cannot write over things and so on. Protect the cells in those columns anyway so no one clears a cell before and after... and that rows formuala cells along with them.

Or move them off to an edge, say 30 columns past the otherwise edge, so no one really notices them. (They might sort, themselves, on just what the see though, and have some issues... that mightn't happen with them inside the data column area as when they'd select what to sort, it would be the columns you already sorted so your sort options should come up and they wouldn't even really notice they were sorting on hidden columns.)

The one big "loss" though with hiding columns is that you'd better have all the possible abbreviations right and no one'd better add one you did not include... because the user would not enter the value and then see one or more of those cells to its right suddenly scream with errors instead of showing a value. Or showing a wrong value because you did a VLOOKUP with approximate match instead of exact match so "nano" popped up, not ohms. So they would not be able to help you maintain things or even notice they'd ended up with something that won't work right for them.

(You could even put the lookup table in a separate spreadsheet that theirs refers to but never opens so when they call up, you can edit that spreadsheet, adding their material to the table/s, saving it, and telling them to press F9 and see if it's all better now.)

Lots of choices, some ways to walk before running with improvements coming as you have time, but at the root, simple to understand and to actually do.

Of course, VBA is the true magic tool here. With it, you could do the sort using its better abilities to create the sorting information it needs and do the sort, all without involving the user at all past clicking a handy button you placed in the spreadsheet labelled "SORT". But VBA is definitely "run" not "walk" (I wouldn't try to accomplish this with it with my skills of the moment) and some organizations don't even allow its use at all.