Search for Common Values in a Column Variables

snaplink22

New Member
Joined
Jul 6, 2018
Messages
16
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,267
Office Version
2013
Platform
Windows
What else is in the cells beside the date and invoice number/ Is there other number groups, or just text?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,267
Office Version
2013
Platform
Windows
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.

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,216
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,267
Office Version
2013
Platform
Windows
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?

Code:
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

New Member
Joined
Jul 6, 2018
Messages
16
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

New Member
Joined
Jul 6, 2018
Messages
16
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.
 

Forum statistics

Threads
1,081,716
Messages
5,360,819
Members
400,600
Latest member
Stuckagainandagain

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top