Asp write text file

Sorting Text in Excel

I found your September 2, 2003, article "Sort Thousands of Rows Individually" (www.pcmag.com/article2/0,4149,1215869,00.asp ) very interesting. I have a similar problem, but the values I need to sort are words and sentences, not numbers. I'd like to be able to sort them alphabetically, using the first letter of the first word in the cell. Can this be done as easily as you showed with numbers?

Specifically, I have a file containing multiple-choice questions and their answers. In each row, column A contains the questions, column B contains the correct answer, and columns C through E contain other possible answers. I want to copy each row's answers into new columns in alphabetical order, to mix them up.

Gregg Klowden

You could write a macro that would sort each row in a range individually, but that would change the original cells, eliminating your own information as to which answer is correct. The previous article solved the problem for numbers by selecting the smallest, the second-smallest, and so on, using the SMALL() function. But there is no such function for text values, so the solution is fairly complex. We'll work up to it gradually.

We'll assume the questions start in cell A2 and the answers in B2:E2, with the correct answer always in column B. The formula =COUNTIF($B2:$E2, "<"&B2) would return the number of items in the range that are smaller (alphabetically) than B2. The smallest value would be ranked 0, as none are smaller. The largest would get 3, as it's larger than the other three.

We're going to need an Excel array containing the ranks of the four answers. Select a range of four cells in a blank row below all the questions. Enter =COUNTIF($B2:$E2, "<"&$B2:$E2), and press Ctrl+Shift+Enter. Excel will put the four rank numbers in those four cells. This is an intermediate result, just to demonstrate that we can get an array containing the ranks.

The alphabetically first item will be the one whose rank is 0. We'll use Excel's MATCH() function to determine the offset of that item within the answers. In column B of an unused row, enter =MATCH(COLUMN()-COLUMN($B:$B), COUNTIF ($B2:$E2, "<"&$B2:$E2),0) and again press Ctrl+Shift+Enter. The COUNTIF function returns the array of ranks, as before. The expression COLUMN()-COLUMN($B:$B) returns 0 in column B, 1 in C, 2 in D, and 3 in E. The value returned by this formula is the one-based offset of the answer having the specified rank. When you copy this formula across into columns C through E, you'll get the locations of the four answers in ranked order. If you copy the four cells down into other rows, you'll get the locations for answers to the corresponding questions.

Finally, we'll use the OFFSET() function to retrieve the items in order. OFFSET() returns the contents of a cell that's a specified number of rows and columns away from a starting cell. We want the same row (offset 0), with a column offset defined by the MATCH calculation above. Highlight the answers, copy them to the clipboard, and paste a copy starting in column A in an otherwise unused row. In that same row in column B, enter this formula: =OFFSET($B2,0,MATCH(COLUMN() - COLUMN($B:$B),COUNTIF($B2:$E2, "<"&$B2:$E2),0)-1). Here again you must press Ctrl+Shift+Enter so Excel will treat this as an array formula. Copy the formula across to column E, then copy all four cells down as far as the last question.

The result is what you requesteda list of questions with the answers sorted in order! You can now delete the intermediate steps. And of course, the next time you need to create a set of questions and answers, you can just make a copy of this workbook and insert the new data.