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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
361
Royalbloodi
R
S
  • Solved
Sort within rows in same column
Replies
2
Views
143
sharshra
S
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
71
stretch2011
S
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
355
Jagadeshrao
J

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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