Search for Common Values in a Column Variables

[snaplink22]

Hello,

I am searching for duplicate invoice numbers in column U, however, the invoice numbers don't always fall in the same order in each cell. For example one cell would be listed as: "1/28/14 Inv 3880" and another would be "#3880 1/28/14", with "3880" being the invoice number. I have roughly 400 rows to cover and any assistance would be very helpful.

 

[JLGWhiz]

What else is in the cells beside the date and invoice number/ Is there other number groups, or just text?

 

[JLGWhiz]

Here is some code to start with. This code assumes that the date and the invoice number will be the only number groups in each cell. It will list the invoice in the secoond column after your last column of regular data.

Sub t()
Dim c As Range, spl As Variant, i As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    For Each c In Range("U2", Cells(Rows.Count, "U").End(xlUp))
        spl = Split(c.Value, " ")
        For i = LBound(spl) To UBound(spl)
            If IsNumeric(Mid(spl(i), 2)) And InStr(spl(i), "/") = 0 Then
                Cells(c.Row, lc + 2) = spl(i)
                Exit For
            End If
        Next
    Next
End Sub

If your invoice numbers are a fixed length, then we can eliminate the # symbol where they are displayed so you can easily find duplicates. If you want the numbers displayed elsewhere, specify6 where and how.

 

[Peter_SSs]

Apart from if there is a fixed length for your invoice numbers as asked by JLGW, could you also gives us a good variety of any different format data that might appear?

We need to get a good understanding of what logic would be used to recognise an invoice number. For example, the only example inv # you have given us is 4 digits. You also have dates possible in the cell. If the date could have a 4-digit year then there is a bit more work to decide between those 4 digits and the inv #

 

[JLGWhiz]

This will get rid of the # symbol when it is the first character of the invoice number. But another question is are there invoice numbers that contain alpha characters?

Sub t()
Dim c As Range, spl As Variant, i As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    For Each c In Range("U2", Cells(Rows.Count, "U").End(xlUp))
        spl = Split(c.Value, " ")
        For i = LBound(spl) To UBound(spl)
            If IsNumeric(Mid(spl(i), 2)) And InStr(spl(i), "/") = 0 Then
        If Left(spl(i), 1) = "#" Then
            Cells(c.Row, lc + 2) = Mid(spl(i), 2)
        Else
                    Cells(c.Row, lc + 2) = spl(i)
        End If
                Exit For
            End If
        Next
    Next
End Sub

 

[snaplink22]

This will get rid of the # symbol when it is the first character of the invoice number. But another question is are there invoice numbers that contain alpha characters?

Thanks for the response and all your help. I needed to get this in quick yesterday and just went with a common delimited approach and spent the time to go through each one.

 

[snaplink22]

Apart from if there is a fixed length for your invoice numbers as asked by JLGW, could you also gives us a good variety of any different format data that might appear?

We need to get a good understanding of what logic would be used to recognise an invoice number. For example, the only example inv # you have given us is 4 digits. You also have dates possible in the cell. If the date could have a 4-digit year then there is a bit more work to decide between those 4 digits and the inv #

Thanks for the response and all your help. I needed to get this in quick yesterday and just went with a common delimited approach and spent the time to go through each one.

 

[JLGWhiz]

Appreciate the feedback,
regards, JLG