• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Excel row selection

How do I select all rows which contain the value 401.9 in one of the fields... The field may have 10 different values all comma deliminated but I want every row which has one value.

Data field example -
611.0, 648.40, 611.0, 648.40, 611.0, 648.40, 611.0, 648.40, V25.09, V25.09, V25.09, V25.09
585.3, 401.9, 462

Each row has 30 columns of data.  The example data is one of those columns.
0
Sean Meyer
Asked:
Sean Meyer
  • 4
  • 3
  • 2
  • +1
1 Solution
 
StephenJRCommented:
What do you want to do once you've selected them?
0
 
Sean MeyerAuthor Commented:
Throw all the rows into a new tab of the worksheet
0
 
Rob HensonIT & Database AssistantCommented:
Assuming you have the comma separated data parsed into columns, you can use Advanced Filter to find the rows that have a value.

You would need a criteria table with the same column headers as the 30 columns of data. Then in separate rows for each column enter the value that you want to find, eg

Col1     Col2     Col3............Col30
401.9
           401.9
                        401.9
                                           401.9

This is telling the filter to do an OR comparison on the data, ie Col1 = 401.9 OR Col2 =401.9 etc.

Thanks
Rob H
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Sean MeyerAuthor Commented:
Anyway to do the row selection in place without splitting up the data into columns?  75,000 rows don't play nice when trying to do this.
0
 
dlmilleCommented:
Why not just use Excel Find for the value 401.9 (look in values, exact match)?  You can also use FindALL for it to give you each of the matches, then you can select each one to go to that row.

Dave
0
 
Sean MeyerAuthor Commented:
That does not scale very well when you have 20,000+ matches
0
 
dlmilleCommented:
Ok - here's a macro that will select all the rows with that value:

You are prompted for something to find, and it selects all rows with that value on the active sheet.

Here's the code:
 
Sub findNumber()
Dim wks As Worksheet
Dim fRange As Range
Dim firstAddress As String
Dim allFound As Range
Dim myFind As String

    Set wks = ActiveSheet
    
    myFind = InputBox("Enter value to find in active worksheet", "Find Values & Select Rows", 401.9)
    
    Set fRange = wks.Cells.Find(what:=myFind, after:=[A1], LookIn:=xlValues, lookat:=xlWhole)
    If Not fRange Is Nothing Then
        firstAddress = fRange.Address
        Set allFound = fRange
        
        Do
            Set fRange = wks.Cells.Find(what:=myFind, after:=fRange, LookIn:=xlValues, lookat:=xlWhole)
            If Not fRange Is Nothing Then
                Set allFound = Union(allFound, fRange)
            End If
        Loop While Not fRange Is Nothing And firstAddress <> fRange.Address
        
        allFound.EntireRow.Select
        MsgBox "ranges selected"
    Else
        MsgBox "desired result not found on active worksheet"
    End If
    
End Sub

Open in new window


See attached demo workbook.

Cheers,

Dave
findMatch-r1.xls
0
 
Rob HensonIT & Database AssistantCommented:
If not parsed into separate columns and no headers you could use AutoFilter on the one column. Use the custom option for "Contains" and the required value. This would still show row 1 even if does not contain value.

Thanks
Rob
0
 
Sean MeyerAuthor Commented:
NOW THATS WHAT I AM LOOKING FOR!

In Excel 2010 -- Select the column to be filtered --- Text filter -- insert text with option for  OR / AND clauses.

THANKS!
0
 
Rob HensonIT & Database AssistantCommented:
Glad to be of help!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now