Do you ever need to sort text without changing the original range? You know: to have an original unsorted range and a sorted one that works in parallel?
This is a variation on the method using =index(), =match(), and =small() that can be used for numbers found in the Chapter 4 of Powell and Baker’s text. The thing is, those are functions that operate on numbers, not text.*
The trick is to use =countif(). This works with numbers or text, and can allow you to count the number of entries in a range that fall alphabetically before the cell that you’re in.
So if you have a range of unsorted text in A1.A5, you can create a list of numbers giving their rank order in B1.B5 by entering this formula in B1 and copying it down the list:
The first argument is telling =countif() to work on that fixed range of text you want to sort.
The second argument uses the ampersand as a text concatenator to combine less than or equal to with the contents of the first cell in your range.
Your result will look something like this:
Now you can use those ranks to put your sorted text anywhere you like. The site I linked above advocates using =vlookup(), but I use =offset() too.
* I never thought that section was very useful. Then I talked to MBA students who have jobs where they have ranges they need to sort in spreadsheets they’re not supposed to goof up (thanks MU!). Now that they’ve opened up my mind a bit, I have found a few cases where I’ve been sorting and desorting that could be improved by creating a parallel sorted range.
See how the choice of “Jams,Preserves” at the top right got the two rows corresponding to that choice to highlight!
These directions from Susan Harkins at TechRepublic are a bit involved, but not hard. First, from your list of non-unique entries, extract a list of unique ones. Second, turn that into a drop-down menu. Third, set up conditional validation for a broader range based on that menu choice.
With the kickstand out, Type Cover keyboard deployed, and Word fired up, the Surface is an absolute joy to use. It's a truly, almost unfathomably good computing experience. I instantly went from "meh, what's the point of this thing?" to "OMG - this is the best device ever!"
That’s interesting … there’s a lot of people who’s biggest problem with tablets is the difficulty of getting their new ideas into it … since everyone agrees that the iPad is a great way to get older ideas out.
Two thoughts about this visualization of Domino’s delivery routes:
1) The bicycle deliverer is a piker. I delivered (by car) in the Buffalo suburbs for over 4 years. I covered about 100 square miles of territory, did over 100 miles of stop-and-go-driving on a weekend night, and the suburbanized swampy terrain had very few neat, homogenous rectangles.
2) What a great video for the transshipment portion of a linear programming or Excel class.
P.S. We have a distribution center (one of the red nodes towards the video’s end) for Wal-Mart located about 35 miles south of my home. I recently found out that it’s actually a distribution center for distribution centers: it handles mostly clothing, and ships nothing directly to stores.
With the development of internet technology, work at home jobs are increasing in the market. Also setting up small business online with ones own bank savings can provide excellent work at home opportunities. Apart from savings, banks offer0 credit card to cater to short term finance needs. Partial tax payments like tax credits are also available to promote online businesses. Market now offers several alternatives to traditional credit card debt which are helpful to work at home businesses.