seattletimebandit
Board Regular
- Joined
- Apr 11, 2013
- Messages
- 69
Hello All,
I've been trying to find a way to search down a single column and based on the text value in a cell, enter a numerical value in the adjacent cell to the left. I want to be able to assign the numbers to allow me to sort the text columns in a particular order, by using the numbers in the adjacent column. I'm thinking a CASE SELECT scenario.
Here are the tables before and after adding the sort number, and the table after sorting the data:
<tbody>
</tbody>
Thanks for your help in advance!
stb
I've been trying to find a way to search down a single column and based on the text value in a cell, enter a numerical value in the adjacent cell to the left. I want to be able to assign the numbers to allow me to sort the text columns in a particular order, by using the numbers in the adjacent column. I'm thinking a CASE SELECT scenario.
Code:
Select Case score
Case "Benzene"
result = "1"
Case "Ethylbenzene"
result = "2"
Case "Toluene"
result = "3"
Case "m, p-Xylene"
result = "4"
Case "o-Xylene"
result = "5"
Case "Gasoline"
result = "6"
'etc.
End Select
Here are the tables before and after adding the sort number, and the table after sorting the data:
Before Adding Sort Number | After Adding Sort Number | After Sorted | |||||
Sort_Order | List | Sort_Order | List | Sort_Order | List | ||
Benzene | 1 | Benzene | 1 | Benzene | |||
Ethylbenzene | 3 | Ethylbenzene | 1 | Benzene | |||
Gasoline | 6 | Gasoline | 2 | Toluene | |||
m, p-Xylene | 4 | m, p-Xylene | 2 | Toluene | |||
o-Xylene | 5 | o-Xylene | 3 | Ethylbenzene | |||
Toluene | 2 | Toluene | 3 | Ethylbenzene | |||
Gasoline Range Organics | 7 | Gasoline Range Organics | 4 | m, p-Xylene | |||
#2 Diesel | 9 | #2 Diesel | 4 | m, p-Xylene | |||
Diesel Range Organics | 8 | Diesel Range Organics | 5 | o-Xylene | |||
Lube Oil | 10 | Lube Oil | 5 | o-Xylene | |||
Benzene | 1 | Benzene | 6 | Gasoline | |||
Ethylbenzene | 3 | Ethylbenzene | 6 | Gasoline | |||
Gasoline | 6 | Gasoline | 7 | Gasoline Range Organics | |||
m, p-Xylene | 4 | m, p-Xylene | 7 | Gasoline Range Organics | |||
o-Xylene | 5 | o-Xylene | 8 | Diesel Range Organics | |||
Toluene | 2 | Toluene | 8 | Diesel Range Organics | |||
Gasoline Range Organics | 7 | Gasoline Range Organics | 9 | #2 Diesel | |||
#2 Diesel | 9 | #2 Diesel | 9 | #2 Diesel | |||
Diesel Range Organics | 8 | Diesel Range Organics | 10 | Lube Oil | |||
Lube Oil | 10 | Lube Oil | 10 | Lube Oil |
<tbody>
</tbody>
Thanks for your help in advance!
stb
Last edited: