Adding Numerical Value to Range of Cells Based on Value in Adjacent Range of Cells

S

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.

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:


[TABLE="width: 863"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Before Adding Sort Number[/TD]
[TD][/TD]
[TD]After Adding Sort Number[/TD]
[TD][/TD]
[TD][/TD]
[TD]After Sorted[/TD]
[/TR]
[TR]
[TD]Sort_Order[/TD]
[TD]List[/TD]
[TD][/TD]
[TD]Sort_Order[/TD]
[TD]List[/TD]
[TD][/TD]
[TD]Sort_Order[/TD]
[TD]List[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Benzene[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Benzene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ethylbenzene[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Ethylbenzene[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Benzene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gasoline[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Gasoline[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Toluene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]m, p-Xylene[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]m, p-Xylene[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Toluene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]o-Xylene[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]o-Xylene[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Ethylbenzene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Toluene[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Toluene[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Ethylbenzene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gasoline Range Organics[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Gasoline Range Organics[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]m, p-Xylene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#2 Diesel[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]#2 Diesel[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]m, p-Xylene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Diesel Range Organics[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Diesel Range Organics[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]o-Xylene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lube Oil[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Lube Oil[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]o-Xylene[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Benzene[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Benzene[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Gasoline[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ethylbenzene[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Ethylbenzene[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Gasoline[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gasoline[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Gasoline[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Gasoline Range Organics[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]m, p-Xylene[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]m, p-Xylene[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Gasoline Range Organics[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]o-Xylene[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]o-Xylene[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Diesel Range Organics[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Toluene[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Toluene[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Diesel Range Organics[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gasoline Range Organics[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Gasoline Range Organics[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]#2 Diesel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]#2 Diesel[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]#2 Diesel[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]#2 Diesel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Diesel Range Organics[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Diesel Range Organics[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Lube Oil[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lube Oil[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Lube Oil[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Lube Oil[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help in advance!

stb
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Assuming your Sort_Order is in column A, List is in column B and data starts in row 2, try the following macro. You can add the additional items to the Select Case loop.
Code: 
Sub AddNumVal()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range, foundRng As Range
    Dim sAddr As String
    For Each rng In Range("B2:B" & LastRow)
        Set foundRng = Range("B:B").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            sAddr = foundRng.Address
            Do
                Select Case foundRng.Value
                    Case "Benzene"
                        foundRng.Offset(0, -1) = 1
                    Case "Ethylbenzene"
                        foundRng.Offset(0, -1) = 2
                    Case "Toluene"
                        foundRng.Offset(0, -1) = 3
                    Case "m, p-Xylene"
                        foundRng.Offset(0, -1) = 4
                    Case "o-Xylene"
                        foundRng.Offset(0, -1) = 5
                    Case "Gasoline"
                        foundRng.Offset(0, -1) = 6
                End Select
                Set foundRng = Range("B:B").FindNext(foundRng)
            Loop While foundRng.Address <> sAddr
            sAddr = ""
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Or a simpler version of mumps code
Code: 
Sub AddNumVal()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    Dim rng As Range
    For Each rng In Range("B2:B" & LastRow)
      Select Case rng.Value
          Case "Benzene"
              rng.Offset(0, -1) = 1
          Case "Ethylbenzene"
              rng.Offset(0, -1) = 2
          Case "Toluene"
              rng.Offset(0, -1) = 3
          Case "m, p-Xylene"
              rng.Offset(0, -1) = 4
          Case "o-Xylene"
              rng.Offset(0, -1) = 5
          Case "Gasoline"
              rng.Offset(0, -1) = 6
      End Select
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
@Fluff: I don't know why I made it over-complicated. Thanks for the input. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Search a range for a specific color of rows, relocate those cells to another sheet, return previous sheet formatting to blank and sort secondary sheet
Replies
4
Views
371
Royalbloodi
R
S
  • Solved
Sort within rows in same column
Replies
2
Views
152
sharshra
S
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
75
stretch2011
S
A
  • Solved
Row Height adjusted based on content - not for a cell but for a range of cells
Replies
8
Views
128
kvsrinivasamurthy
K
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
323
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top