none
Best way to search one column and based upon this column's contents, place corresponding text in another column's cells RRS feed

  • Question

  • I have a spreadsheet that has numerous columns. There is one column that contains over 3,000 four character text, some of which are duplicates. I need to search this column and based upon the cell contents, enter a code in another column in the cell of the same row that refers to the four character text. I will be pulling this same spreadsheet at different times throughout a period of time and will need to put the code/function into a macro. What is the best way to do this? Is there VBA code that would accomplish this?

    I have tried the following functions:

    =VLOOKUP($T4, 'W2'!$A$1:$B$386, 2, FALSE)

    and

    =INDEX('W2'!B1:B384,MATCH('W1'!T4,'W2'!A1:A384,0))

    I get the #N/A error message even though I know the contents in W1, T4 exist in W2, A1:A384. When the contents are found in W2, I need for Column B contents corresponding to Column A to be placed in a cell in W1. What am I doing wrong?

    Thanking you in advance for your help.


    tsphil25



    • Edited by teesp Saturday, November 26, 2016 7:07 AM
    Saturday, November 26, 2016 3:05 AM

All replies

  • Option Explicit
    
    Sub Test()
      Dim All As Range, Here As Range
      
      'Find all 'abcd' in column A
      Set All = FindAll(Columns("A"), "abcd")
      'Found?
      If All Is Nothing Then
        MsgBox "Not found"
        Exit Sub
      End If
      'Write something in this rows to column B
      Set Here = Intersect(Columns("B"), All.EntireRow)
      Here.Value = "<- here"
    End Sub
    
    Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      FirstAddress = c.Address
      Do
        Stack.Add c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function


    Saturday, November 26, 2016 7:26 AM
  • Another more simple and universal way is to include some manual steps:

    a) Use the built in dialog and search for whatever you want, but click the FindAll button. Then select all results inside the dialog and close the dialog. (The result is that all found cells are selected).

    b) Enlarge the selection to the entire row, execute this macro:

    Sub SelectionEntireRow()
      Selection.EntireRow.Select
    End Sub

    c) If we preserve the selected rows we can select a column and find the intersection of both:

    Sub SelectionIntersect()
      Dim Where As Range
      On Error GoTo ExitPoint
      Set Where = Application.InputBox("Select cells for intersect", , Selection.Address(0, 0), Type:=8)
      Set Where = Intersect(Selection, Where)
      If Where Is Nothing Then
        MsgBox "Doesn't intersect"
      Else
        Where.Select
      End If
    ExitPoint:
    End Sub

    You can also combine the FindAll function with these little macros, it is up to you to choose the combination which fit your expectations as best.

    Andreas.

    Saturday, November 26, 2016 9:20 AM
  • Dear Andreas:

    Thank you for your help. I tried the first VBA code by going into the Developer and pasting the code into Module 1; however, when I run it, I get the error messagebox "Not found." Is there something else that I need to do to execute this code?

    I am not sure how to execute your second set of instructions. Can you add a little more detail? If I use the Find and Replace Dialog, and place the value in Column T4, "4007" in the Find what: to search within the workbook, and select Match entire cell contents, Find All, I receive the following information:

    

    W2, A279 contains the matching value "4007". Column B in this worksheet contains the value that I need to place in the first worksheet, W1, in column R4.

    What would I do from here? Is there an easier way?

    Thank you so very much for your help!!!


    tsphil25

    Saturday, November 26, 2016 6:08 PM
  • Dear Andreas:

    Thank you for your help. I tried the first VBA code by going into the Developer and pasting the code into Module 1; however, when I run it, I get the error messagebox "Not found." Is there something else that I need to do to execute this code?

    I am not sure how to execute your second set of instructions.

    a) You missed to read my comments in the code ad customize the macro.

    b) Here's a video:
    https://dl.dropboxusercontent.com/u/35239054/Samples/142f3442-6fda-4efd-bfb8-2845196f97d1.mp4

    Andreas.

    Sunday, November 27, 2016 10:49 AM