Back to Excel Newsletter Archives

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

** Current Special!** Complete Excel __Excel Training Course__ for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

**Excel Finance & Statistic Learning**. The source code of these Excel files is unprotected for learning purposes.

Got any Excel Questions? **Free ****Excel Help **

**See Also: Vlookup | Index & Match | 7 Nested IF Limitation | Lookup Table | ****Sliding Scale Lookup **

**Lookup With Cell**

While we can use any of the links above for lookup formulas, all require a table of cells in a Worksheet. If you only have small number of items to return based on the value of another cell, we can do the lookup without leaving the cell!

**Choose & Match**

The formulas we can use to perform the lookup within the cell is the CHOOSE function, nested with the MATCH function. See Excel help if unfamiliar we these functions. Ok, let's assume you have a changing value in A1 and depending on that value we wish to return a result to B1. For example, keeping it simple, we have a Validation List that a user can choose from any one of these Cat, Dog, Mouse, Horse or Rabbit. Based on their choice we wish to show a different result in B1. In B1 Enter this formula;

**=CHOOSE(MATCH(A1,{"Cat","Dog","Mouse","Horse","Rabbit"},0),"Cat Food","Dog Food","Mouse Food","Horse Food","Rabbit Food")**Enter, or choose any value from Cat, Dog, Mouse, Horse or Rabbit and you will see how it works. The use of **{"Cat","Dog","Mouse","Horse","Rabbit"} **in the MATCH Formula is know as an **array constant **(See Excel help for details). Not be confused with Array Formulas

**Keeping it Clean & Global**

The main problems with the above formula is it's long, but most importantly to edit the lookup values or the array constant, we would need to do each cell individually, or use** Edit>Replace**, if possible. This is where we can Range Names , or specifically **Named Constants . **However, when done, we can no longer use the CHOOSE Function. We use the INDEX Function instead.

1) Go **Insert>Name>Define** and type ** Pet** in

2) In

3) Type

4) In

Now, in the cell use

**=INDEX(PetFood,MATCH(A1,Pet,0))**

When/if we need to edit the named constants ** PetFood** or

**Lookup Scale**

In the above examples we have used text values. However, it is often needed that we need to lookup numbers that match a scale. That is, all results between 0 and 99.99 should return one result, while those between 100 and 199.99 another and so on... Let's say we need to match the sales amount by person to know what percentage their commission is.

1) Go **Insert>Name>Define** and type * Commission* in

2) In R

3) Type

4) In

Now, in the cell use

**=INDEX(Commission,MATCH(A1,Sales,1))**

This will return a % between 0 and 100, based on the value in A1.

Sales $ | % Commission |

0-99.99 | 0% |

100-199.99 | 10% |

200-299.99 | 20% |

300-399.99 | 30% |

400-499.99 | 40% |

500-599.99 | 50% |

600-699.99 | 60% |

700-799.99 | 70% |

800-899.99 | 80% |

900+999.99 | 90% |

1000+ | 100% |

It is important to note that both array constants (in the **Refers to**) are in Ascending order and we have use **1** for the **optional** *Match_type* argument for the MATCH Function. You can use Descending order, in which case *Match_type *must be **-1**

**Current Special!** Complete Excel __Excel Training Course__ for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download Got any Excel Questions? **Free ****Excel Help . ** **See Normal Excel Sort and Extract Numbers From Text **

**Sorting Alphanumeric**

Excel has a problem trying to sort alphanumeric cells in cells by the number portion only. The reason is simply because Excels Sort feature evaluates each cell value by reading left to right. However, we can over-come this in a few ways with the aid of Excel Macros .

**Fixed Length Text**

If the alphanumeric cells are all a fixed length we can simply use another column to extract out the numeric portion of the alphanumeric text and then sort by the new column. For example, say we can alphanumeric text in Column A like **ABC196**, **FRH564 etc**. We can simply add the formula below to Column B.

**=--RIGHT(A1,3)**

**OR**

**=--Left(A1,3) ***for fixed length alphanumeric text like 196GFT*

*OR*

**=--MID(A1,5,4) **for alphanumeric text like **a-bg1290rqty **where you know the number Start s at the 5th character and has 4 numbers

Then Fill Down as far as needed. Then we can select Column B, copy and **Edit>Paste Special - Values**. Next we sort Columns A & B by Column B and then delete Column B.

*NOTE: the double negative (--) ensures the number returned is seen as a true number.*

**Sort Alphanumeric**

**Any Length Alphanumeric Text**

A problem comes about when the numeric portion and/or the text portion can be any length. In these cases a macro is best. The code below should be copied to any standard Module (**Insert>Module**). Then simply run the *SortAlphaNumerics* Procedure.

It should be noted that the ** ExtractNumber** Function has 2

Alphanumeric Text | Formula | Result |

a-bg-12909- | =ExtractNumber(A1,,TRUE) | -12909 |

a-bg-12909- | =ExtractNumber(A2) | 12909 |

a.a1.2... | =ExtractNumber(A3,TRUE) | 1.2 |

a.a1.2... | =ExtractNumber(A4) | 12 |

a.a-1.2.... | =ExtractNumber(A5,TRUE,TRUE) | -1.2 |

abg1290.11 | =ExtractNumber(A6,TRUE) | 1290.11 |

abg129013Agt | =ExtractNumber(A7) | 129013 |

abg129012 | =ExtractNumber(A8) | 129013 |

**Alphanumeric Sorting Code**

'MUST be at top of same public module housing _Sub SortAlphaNumerics and Function ExtractNumberDim bDec As Boolean, bNeg As BooleanSub SortAlphaNumerics()Dim wSheetTemp As WorksheetDim wsStart As WorksheetDim lLastRow As Long, lReply As LongDim rSort As Range '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Sorts Alphanumerics Of Column "A" of active Sheet. 'ExtractNumber Function REQUIRED 'http://www.ozgrid.com/VBA/ExtractNum.htm '''''''''''''''''''''''''''''''''''''''''' Set wsStart = ActiveSheet On Error Resume Next Set rSort = Application.InputBox _ ("Select range to sort. Any heading should be bolded and included", "ALPHANUMERIC SORT", _ ActiveCell.CurrentRegion.Columns(1).Address, , , , , 8) If rSort Is Nothing Then Exit Sub If rSort.Columns.Count > 1 Then MsgBox "Single Column Only" SortAlphaNumerics End If 'Application.ScreenUpdating = False Set rSort = Range(Cells(rSort.Cells(1, 1).Row, rSort.Column), _ Cells(Rows.Count, rSort.Column).End(xlUp)) lReply = MsgBox("Include Decimals within numbers", vbYesNoCancel, "OZGRID ALPHANUMERIC SORT") If lReply = vbCancel Then Exit Sub bDec = lReply = vbYes lReply = MsgBox("Include negative signs within numbers", vbYesNoCancel, "OZGRID ALPHANUMERIC SORT") If lReply = vbCancel Then Exit Sub bNeg = lReply = vbYes lLastRow = rSort.Cells(rSort.Rows.Count).Row Set wSheetTemp = Worksheets.Add rSort.Copy wSheetTemp.Range("A1") With wSheetTemp.Range("B1:B" & lLastRow) .FormulaR1C1 = "=ExtractNumber(RC[-1]," & bDec & "," & bNeg & ")" .Copy .PasteSpecial xlPasteValues Application.CutCopyMode = False End With bNeg = False bDec = False With wSheetTemp.UsedRange .Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal .Columns(1).Cut wsStart .Range(rSort.Cells(1, 1).Address) End With Application.DisplayAlerts = False wSheetTemp.Delete Application.DisplayAlerts = True Application.ScreenUpdating = TrueEnd SubFunction ExtractNumber(rCell As Range, _ Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As DoubleDim iCount As Integer, i As Integer, iLoop As Integer Dim sText As String, strNeg As String, strDec As String Dim lNum As String Dim vVal, vVal2 '''''''''''''''''''''''''''''''''''''''''' 'Written by OzGrid Business Applications 'www.ozgrid.com 'Extracts a number from a cell containing text and numbers. '''''''''''''''''''''''''''''''''''''''''' sText = rCell If Take_decimal = True And Take_negative = True Then strNeg = "-" 'Negative Sign MUST be before 1st number. strDec = "." ElseIf Take_decimal = True And Take_negative = False Then strNeg = vbNullString strDec = "." ElseIf Take_decimal = False And Take_negative = True Then strNeg = "-" strDec = vbNullString End If iLoop = Len(sText) For iCount = iLoop To 1 Step -1 vVal = Mid(sText, iCount, 1) If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then i = i + 1 lNum = Mid(sText, iCount, 1) & lNum If IsNumeric(lNum) Then If CDbl(lNum) < 0 Then Exit For Else lNum = Replace(lNum, Left(lNum, 1), "", , 1) End If End If If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1)) Next iCount ExtractNumber = CDbl(lNum)End Function

Until next month, keep excelling!

Got any Excel Questions? **Free **** Excel Help **

**Current Special!** Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,**30 Day Money Back Guarantee** & Free Excel Help for LIFE!

**Software Categories ** Search Software

Excel **Add-ins** || Excel **Training ** || Excel **Templates** || **Employee Scheduling Software** || Excel** Password Recovery** and Access & Word etc|| Excel Recovery and Access & Word etc || **Financial** Software || Financial **Calculators** || **Conversion** Software || **Construction** **Estimating** Software || **Drilling Software** || **Real Estate** Investment Software || Time** Management** Software || **Database** Software || **Neural** Network Software || **Trading** Software || **Charting** Software || **Windows** & **Internet** Software || ** Barcodes ** Fonts, ActiveX, Labels and DLL's || **Code Printing** Software || **Outlook** Add-ins

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.