I’ve been tasked with streamlining the use of a spreadsheet we use daily at work, and I’m hitting a wall with one of the big issues.
I’ve got columns for “receive date”, “receiving number”, “collection date”, among others, and I would like to be able to switch easily between sorting by receiving number and by collection date. the problem I’m getting is that if I go by receiving number (YY-####), it’s putting all the year-2000+ receiving numbers before the ones from the 90s. I’m not sure how to fix that. I manually cut-and-pasted the sections so they line up right, for now, but with 15000 rows, it’s a pain in the ass. I can sort fine by receiving date, then hit “undo” to go back to my handmade sort-by-receiving-number look, but I know there’s a better way.
I’ve been trying to Google this for help, but I’m obviously not using the right search terms, because I’m getting nowhere. Any ideas?
Do you mean that you’re using the last two digits of the year for the first two digits of your receiving number, with some other number for the rest? Are the other numbers consecutive for the year as items are received?
Like this?
99-4321
00-1234
00-1235
01-2345
Is there a reason that you need to sort by receiving number instead of receiving date? For instance, could you do a two-level sort by receiving date (to get the proper date order) and then by receiving number (to get the correct order for each day)?
Yes, that’s what the receiving numbers look like. Pretty much everything we do involves looking stuff up by that number, because every unit of plasma that comes in gets assigned a sequential number and then that is its ID through its lifetime here at the facility.
We only need to sort by date if we get a recall for a specific shipment involving multiple units, or when we’re going through inventory every month to discard expired (>15yrs) units.
I think that in theory the double sort you describe would work, but it’s a little more complicated and not everyone using the worksheet is great with Excel. The ghost column has the same problem - I’d know how to use it, but training others to use column X when they want to sort by receiving number may be too much to ask. I’ll check with the boss and see what he thinks of these options.
Is there a way to just format those numbers so Excel knows the two leading digits mean dates and to act accordingly?
Yes, but it’s not what you want. There is a way using custom formatting that identifies the first two digits as a date (year). Unfortunately there is no format that assigns the correct century.
So while Excel will store that two number prefix as a date it will assume that all two digit years are prefixed by 19.
If you have any code savvy you could write up a macro which will do a two part sort.
Then just add a button or assign a cntrl-s or other keystroke to run the sort.
I don’t think there’s a way to magically make Excel understand what those two digits are.
The options I’m coming up with off-hand would be: 1) the extra column as already suggested; 2) the double-column sort; 3) stick the extra digits into your received numbers so that they’ll sort properly (could be automated); 4) a special sorting button that would run some VBA to auto-magically add the missing digits & sort.
I can help with the last couple of ideas, if needed.
Was checking back for any updates and saw this one I’d missed before.
This. This is the best suggestion.
Unless you really, really need constant access to 12 year old data combined with current data. Archive everything pre-2000 to a separate worksheet, then the sorts on each sheet will work fine.
)