I STILL COULDNT GET THAT TO WORK - I SORTED IT OUT IN A SLIGHT ROUND THE HOUSES WAY BY USING A VLOOKUP FUNCTION =VLOOKUP(MIN(CODE(A1:F1)),CODE_TRANSITION,2) WHERE I SET UP A RANGE "CODE_TRANSITION" USING THE CODE FORMULA. THIS IS WORKING IF I DO THE SHIFT CONTROL AND ENTER FOR THE ARRAY FORMULA - I HAVE NEVER USED ARRAY FORMULAS BEFORE - SO THANKS BOTH OF YOU FOR THE HELP.

0

Please do not post in all caps. I do not know what you did wrong since the formula I offered works fine for me.

0

Hi, You can use the Sort on the Data menu This treats upper and lower case as the same letter by default, but read on ... Here is my starting list on the left and sorted list on the right a a C C g E h g Y G O h p H y j E k w O T p G T k w j Y H y To use the built-in sort, select the range of cells to be sorted then goto Data - Sort The box offers options such as whether your columns of data have headings - if so it leaves them as headings and doesn't sort them, and if you have selected more than one column of data you can sort on one column or more than one column. If you need to sort both upper and lower case you can create a custom list with A to Z all upper case followed by a-z all lower case. Create a column of these 52 letters. Goto tools - options - Custom lists tab Use the import section to select your 52 letters and Import - Add - OK Now select the cells to be sorted, then Data - Sort, but this time use the Options (at the bottom) and in First Key sort order you will be able to select your new A-Z a-z list. Check the case sensitive check box and OK. Your list will appear like this: C E G H O T Y a g h j k p w y Of course you could have made your custom list with lower case first, and you could have included numbers (numbers must be entered as text. Prefix number with ' e.g., '1), if for instance you wanted cells with numbers to be sorted before text, rather than the default after text. For help on the sort function - in Excel enter 'Sort a Range' This sort function doesn't require the use of extra cells as data is sorted in-situ. The 52 A-Z a-z cells you created can be deleted once you have imported them as a custom list. Regards

0

Try this: Place your target letters in Cells A1 - D1 In Cell E1 enter the formula: =CHAR(MIN(CODE(A1),CODE(B1),CODE(C1),CODE(D1))) Should get you what you are looking for. MIKE http://www.skeptic.com/

1

Sorry about Caps - don't usually do that. Yeah that works - but I now have the problem - as I did with my own work around, that if one of the cells is blank it doesnt work - I want to use it for exam marks and they will often be blankand I will always be looking along rows and not up columns - so could you help me with that one? .

0

Hi, The Sort function puts empty cells at the end of the sorted list and doesn't come up with any errors. Regards

0

I dont want to sort them - I want to be able to return the lowest letter from a row of cells. The Char formula worked but if there were blanks it returned an error message. I need a work around for that please.

0

Hi, The following user defined function will return the smallest letter. Lower case are 'smaller' than upper case and blank cells and cells with a space in them are ignored. Numbers and any other characters are ignored. Enter the code in a standard module (not in PERSONAL.xls) In your worksheet enter the formula =AlphaSmall(L4:AJ4) Change L4:AJ4 to the range of cells with your letters in This function has been setup to work on a single row of data only There are some comments in the code, so you should be able to change it for slightly different logic. My test data consisted of 25 cells with two blank cells, one number and random letters The smallest letter was returned by the function Public Function AlphaSmall(rngSort As Range) As String ' Routine to return the text of the 'lowest' letter in a range of cells ' The sort order is Upper case A-Z, lower case a-z, lower case z being the smallest ' non-alpha cells are considered as 'larger' ' only single letters or the first letter in a cell are considered Dim varSortArray() As Variant Dim rngCell As Range Dim m As Integer Dim n As Integer On Error GoTo ErrHnd 'single row only If rngSort.Rows.Count > 1 Then GoTo ErrHnd 'size an array to match number of data elements ReDim varSortArray(rngSort.Columns.Count, 2) 'put data into array (as text) n = 1 For Each rngCell In rngSort.Cells varSortArray(n, 0) = rngCell.Text 'convert text to a numeric value If varSortArray(n, 0) = "" Then varSortArray(n, 1) = 0: GoTo NextCell If Asc(varSortArray(n, 0)) > 64 And Asc(varSortArray(n, 0)) < 91 Then varSortArray(n, 1) = Asc(varSortArray(n, 0)) - 64: GoTo NextCell If Asc(varSortArray(n, 0)) > 96 And Asc(varSortArray(n, 0)) < 123 Then varSortArray(n, 1) = Asc(varSortArray(n, 0)) - 69: GoTo NextCell 'sort anything else at the beginning varSortArray(n, 1) = 0: GoTo NextCell NextCell: n = n + 1 Next 'compare cells and create order numbers for each record For m = 1 To UBound(varSortArray, 1) For n = 1 To UBound(varSortArray, 1) If varSortArray(m, 1) >= varSortArray(n, 1) Then varSortArray(m, 2) = varSortArray(m, 2) + 1 Next n Next m 'get the largest order number n = 0 For m = UBound(varSortArray, 1) To 1 Step -1 For n = 1 To UBound(varSortArray, 1) If varSortArray(n, 2) = m Then AlphaSmall = varSortArray(n, 0) Exit Function End If Next n Next m AlphaSmall = CVErr(xlErrValue) Exit Function ErrHnd: AlphaSmall = CVErr(xlErrNA) End Function Regards

0

It is the =CODE() function that chokes on a blank cell and throws the #VALUE error up. A work around would be something like: =CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1))))) I used the tilde character because it's ASCII number is 126. I also added the =UPPER() function so even if you enter a lower case a it will be translated into an upper case. MIKE http://www.skeptic.com/

1

This was a good solution - The only problem with this is that if all the cells in the range are blank it returns the ~ symbol and I need them to be blank - can you help with this please

0

Hi, Wrap the formula you were given in an If statement. If it returns "~" then "" else the original formula =IF(CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1)))))="~","",CHAR(MIN(IF(A1="",CODE("~"),CODE(UPPER(A1))),IF(B1="",CODE("~"),CODE(UPPER(B1))),IF(C1="",CODE("~"),CODE(UPPER(C1))),IF(D1="",CODE("~"),CODE(UPPER(D1)))))) Regards

0

Here is a solution for getting rid of the tilde: =CHAR(MIN(IF(A2="","160",CODE(UPPER(A2))),IF(B2="","160",CODE(UPPER(B2))),IF(C2="","160",CODE(UPPER(C2))),IF(D2="","160",CODE(UPPER(D2))))) You have to be careful in removing the data from the cells. You MUST delete the contents of the cell. If you simply Space over the letter, then the Space Character becomes the lowest ASCII number and that is what will be displayed. MIKE http://www.skeptic.com/

0

Here is a better solution, you no longer have to worry the space characters. =CHAR(MIN(IF(OR(A1="",A1=" "),"160",CODE(UPPER(A1))),IF(OR(B1="",B1=" "),"160",CODE(UPPER(B1))),IF(OR(C1="",C1=" "),"160",CODE(UPPER(C1))),IF(OR(D1="",D1=" "),"160",CODE(UPPER(D1))))) MIKE http://www.skeptic.com/

0

Hi, This solution allows you to use a range, rather than entering individual cell addresses. It copes with empty cells and cells with a single space in them. With data in A1 to D1 enter this into any cell: =CHAR(MIN(CODE(UPPER(IF(ISBLANK(A1:D1),"{",IF(A1:D1=" ","{",(A1:D1))))))) then with your cursor in the formula bar and inside the formula enter ctrl+shift+enter i.e., press all three keys together. If you get this right the formula will be surrounded by { and } This is an array formula, but it can be dragged and copied like a normal formula. Regards

1

That was a good way - much easier to replicate

0

You're welcome

0

Thanks everyone for your help. I cant believe something which seems should be so simple would be so complicated. All of the techniques work but leave a sign -either { or1 or ~. But after i have replicated the foumula i wiil filter the symbols and delete them leaving the correct letter only.

0

Hi, With this minor change, a row of all empty cells returns an apparently empty cell. =CHAR(MIN(CODE(UPPER(IF(ISBLANK(A1:D1),CHAR(160),IF(A1:D1=" ",CHAR(160),(A1:D1))))))) Character 160 prints as nothing in the standard character set As before it is an array formula - Ctrl + Shift + Enter Regards

0