0

Afternoon all,

In excel 2010, I'm attempting to take column B and compare it to columns C, D, E and F. Column B has 2350(rows) part numbers; for example tsp2435a, rst5674d1, latt3454, etc.. Columns C, D, E and F have similar names but not exactly and have 50,000 rows; For example, rst-5674.d1 or latt_3454, etc...

How can I get column b to compare itself against columns C, D, E and F display where there may be a match? I'm assuming some kind of wild card search. I'm an SQL guy. SQL is too rigid to perform this type of analysis. VB or excel formulas is the only thing I can think f. Any information that can belt ivied could be greatly appreciated. Even a push in the right direction. The not all in advance.

user2847718
  • 13
  • 2
  • What kind of output do you want? Highlight matches... or ? Are you comparing row by row or single 'B' cell against all values in any row of C, D, E or F? – Automate This Oct 04 '13 at 18:49
  • Single B cell compared to all columns. That being b2 to b2350. Highlights would be fine or even if it tells me in another sheet the possible cell number that any one value in column is displayed in column c. D, e, or f. – user2847718 Oct 04 '13 at 19:02
  • This is an interesting question. Quick question from me. What all `Separators` can Col C-F have? I can see 3 (`_`,`.` and `"-"`) if you have a predefined list then it is a piece of cake. – Siddharth Rout Oct 04 '13 at 19:05
  • The possible separators are the three you mentioned above; (_, . and "-"). However, there is no predefined list. There seems to be no pattern to the separators or immediate logic that I can see. – user2847718 Oct 04 '13 at 19:44

1 Answers1

0

Like I mentioned in my comment, if you know what all the separators are then it is a piece of cake.

For testing demonstration purpose I have this simple example.

enter image description here

In this example, I will output the cell addresses of the matches in Column D

Since this is an example/demonstration, I am using Hardcoded values to loop through the column. To actually find the last row in a column, see THIS LINK

Logic:

  1. Store the separators _,.,- in a string
  2. Split it using "," as a delimiter and store it in an array
  3. When looping though the column values, simply loop through the array and replace the separators by ""
  4. Once you get the base string, simply use .Find to search Col A for that string.

Code:

Option Explicit

'~~> This is the list for your separators separated by comman
Const sep As String = "_,.,-"

Sub Sample()
    Dim ws As Worksheet
    Dim MyAr
    Dim SearchString As String
    Dim aCell As Range
    Dim i As Long, j As Long

    '~~> Split the separator using "," and store it in an array
    MyAr = Split(sep, ",")

    '~~> Set this to the relevant workbook
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Loop through Col C
        For i = 1 To 3
            If Len(Trim(.Range("C" & i).Value)) <> 0 Then
                SearchString = .Range("C" & i).Value
                '~~> Loop through the array and replace all separator by ""
                For j = LBound(MyAr) To UBound(MyAr)
                    SearchString = Replace(SearchString, MyAr(j), "")
                Next

                '~~> Find the result txt in Col B
                Set aCell = .Columns(2).Find(What:=Trim(SearchString), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

                '~~> If found then output to cell D
                If Not aCell Is Nothing Then
                    .Range("D" & i).Value = "Found in Cell " & aCell.Address
                End If
            End If
        Next i
    End With
End Sub

Screenshot:

enter image description here

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Siddharth, let me get to my current destination and I will let you know the outcome. It may not be until tomorrow morning before I can provide results. Thank you very much. – user2847718 Oct 04 '13 at 20:02