I want to convert string codes to integer codes
Here is my Excel file
Level1 Level2 Level3 Level4 Time1 Time2 Time3 Time4
A B E C 12 14 12 13
D D E C 11 17 16 9
E C C B 14 13 19 8
C E B D 11 12 10 7
B A A D 10 11 7 6A= 4, B=3, C=2, D=1 , E= 0
get this in sheet2
Level1 Level2 Level3 Level4 Time1 Time2 Time3 Time4
4 3 0 2 12 14 12 13
1 1 0 2 11 17 16 9
0 2 2 3 14 13 19 8
2 0 3 1 11 12 10 7
3 4 4 1 10 11 7 6Use the ASCII table and some basic maths.
dim i as long
for i=65 to 69
worksheets("sheet1").cells.replace what:=chr(i), replacement:=(69-i)
next i
This isn't as fast as a dictionary, but its clocking at 1 second for 10,000 rows across 4 columns.
Searching in columns A - D down to last row (determined by Col A)
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim SearchRange As Range: Set SearchRange = ws.Range("A2:D" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
Application.ScreenUpdating = False
SearchRange.Replace What:="A", Replacement:=4, LookAt:=xlWhole
SearchRange.Replace What:="B", Replacement:=3, LookAt:=xlWhole
SearchRange.Replace What:="C", Replacement:=2, LookAt:=xlWhole
SearchRange.Replace What:="D", Replacement:=1, LookAt:=xlWhole
SearchRange.Replace What:="E", Replacement:=0, LookAt:=xlWhole
Application.ScreenUpdating = True
Set up an area of any worksheet (for example Sheet2) with your chosen mapping of letters to numbers, like this:
+--------+-------+
| Letter | Value |
+--------+-------+
| A | 4 |
| B | 3 |
| C | 2 |
| D | 1 |
| E | 0 |
+--------+-------+
Then, suppose on one of your main worksheets you have a letter in cell B3="D". You can enter a formula into a different cell, perhaps cell C3, with your lookup formula: =VLOOKUP(B3,Sheet2!A2:B10,2,FALSE). (The range A2:B10 in this case would be replaced with the range of your entire letter-to-number table). The formula will return the number value.
Enter this in Sheet2!A2
=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,69-CODE(Sheet1!A2))
Fill over/down as needed.
Dictionarythat maps the values to the letters. Makes the translation quick and easy. If you want a non-code solution, then create a table on a separate spreadsheet and useVLOOKUPto get the value for each letter.