Split cell into arrays and compare them to cells in separate columns

[f00dFights]

Im working on a small project and im having trouble going about implementing this.

Im trying to create a function which scans a column (job-trav-seq) and splits the values in each cell within a given range. It then compares these values to comparable cells in separate columns (so for instance job-trav-seq would have a cell 58546-05-10; this function would remove the dashes and compare the 58546 to job number, 05 to traveller ID and 07 to sequence No.

If there is a match then the loop should carry to the next cell until the process is complete, i as thinking of using range.find but the trouble im having is how to go about implementing it

any help would be most appreciated

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[f00dFights]

I have included a before and after of what im trying to do
SampleDoc -> SampleDocOutput

Basically, the function needs to first takes the A column (JobTravSeq) and breaks it apart into individual variables. (variable 1 should be compared with values in column B, values in variable 2 should be compared with column C and values in variable 3 should be compared with column D)

A loop should go up through the column cells as long as variable 1 = values in column B and variable 2 = values in column C (this is rowStart); this should be stored as a variable

A second loop should occur (rowEnd); which should loop down though the column cells as long as variable 1 = values in column B and variable 2 = values in column C; this should be stored as a variable

The code should then traverse between rowStart and rowEnd and check if variable 3 = values in column D, if it does then place an asterisk (or something similar) in front of the value to mark it as a current task

 

[f00dFights]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

sorry for the late reply

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, dic As Object, i As Long, rowCount As Long, val1 As String, val2 As String, val3 As String
    Dim rng As Range
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        val1 = Split(v(i, 1), "-")(0)
        With ActiveSheet
            .Range("A1").CurrentRegion.AutoFilter 1, val1 & "*"
            rowCount = [subtotal(103,A:A)] - 1
            With rng.SpecialCells(xlCellTypeVisible)
                lRow = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count - 1
            End With
            If rowCount = 1 Then
                val2 = Split(v(i, 1), "-")(1)
                val3 = Split(v(i, 1), "-")(2)
                If val1 = v(i, 2) And val2 = v(i, 3) And val3 = v(i, 4) Then
                    Range("D" & i + 1) = "* " & v(i, 4)
                End If
            Else
                If Not dic.exists(val1) Then
                    dic.Add val1, Nothing
                    Range("D" & lRow) = "* " & v(i + rowCount - 1, 4)
                End If
            End If
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

 

[f00dFights]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim lRow As Long, v As Variant, dic As Object, i As Long, rowCount As Long, val1 As String, val2 As String, val3 As String
    Dim rng As Range
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        val1 = Split(v(i, 1), "-")(0)
        With ActiveSheet
            .Range("A1").CurrentRegion.AutoFilter 1, val1 & "*"
            rowCount = [subtotal(103,A:A)] - 1
            With rng.SpecialCells(xlCellTypeVisible)
                lRow = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count - 1
            End With
            If rowCount = 1 Then
                val2 = Split(v(i, 1), "-")(1)
                val3 = Split(v(i, 1), "-")(2)
                If val1 = v(i, 2) And val2 = v(i, 3) And val3 = v(i, 4) Then
                    Range("D" & i + 1) = "* " & v(i, 4)
                End If
            Else
                If Not dic.exists(val1) Then
                    dic.Add val1, Nothing
                    Range("D" & lRow) = "* " & v(i + rowCount - 1, 4)
                End If
            End If
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

Im guessing val1 stands in for jobtravseq?

 

[mumps]

val1 represents the numbers to the left of the first hyphen, so if jobtravseq is 12345-0-10, then val1 would be 12345.

 

[f00dFights]

val1 represents the numbers to the left of the first hyphen, so if jobtravseq is 12345-0-10, then val1 would be 12345.

Thank you, ill give it a try

 

[f00dFights]

val1 represents the numbers to the left of the first hyphen, so if jobtravseq is 12345-0-10, then val1 would be 12345.

i tried your code and it sets an asterisk on the last column instead of each row where column d and variable 3 are a match; other than that its great

 

[f00dFights]

i tried your code and it sets an asterisk on the last column instead of each row where column d and variable 3 are a match; other than that its great

or even whether it could be matched with currOp?