How To Display all the Records of Text file which does not Match/Exists in Worksheet ?

[NimishK]

Hello

Your Help shall be appreciated.

I am trying to display all the Records of Text file which does not exists/Match in Worksheet and to paste them

I've implemented the following code but somehow not successful.
The following code displays the Non-Matched Value of Worksheet against the Textfile and The rest Non-Matched values are not displayed.

Private Sub cmdNonMatchRecordsOfTextFile_Click()

Dim txtFileName As String, strDataTxtFile As String, nosline As String, lineCount As Integer
Dim txtMatched As String, txtNotMatched As String, msg As String

Dim rng As Range, c As variant, lstRow As Long

txtFileName = "C:\Matching\Records.txt"

Open txtFileName For Input As #1
While Not EOF(1)
     Line Input #1, strDataTxtFile
     nosline = nosline & strDataTxtFile & vbCrLf
 Wend
Close #1

TextBox1.Text = TextBox1.Text & nosline

Worksheets("Sheet1").Activate

With Worksheets("Sheet1")
lstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set rng = Worksheets("Sheet1").Range("A1").currentRegion.columns(1)
''Or
''Set rng = Worksheets("Sheet1").Range("A1:A" & lstRow)

For Each c In rng.Rows

If InStr(nosline, c) > 0 Then
     ''''''txtMatched = txtMatched & c & ","
Else
     txtNotMatched = txtNotMatched & c & ","
End If
Next

'''''txtMatched = Left(txtMatched, Len(txtMatched) - 1)
txtNotMatched = Left(txtNotMatched, Len(txtNotMatched) - 1)

''''msg = "Following Records Found in Sheet1  " & txtMatched
msg = "Following Records Not found in Sheet1"   & txtNotMatched

MsgBox msg

End Sub

When following below syntax changed to
Set rng = Worksheets("Sheet1").Range("A2:A" & lstRow) then Nothing Displays.

Else
with following syntax
Set rng = Worksheets("Sheet1").Range("A1").currentRegion.columns(1)
It only displays the NonMatched value of A1 = "Date" which is in the header row and Does not display other non matched Dates

What is desired is to Display all the Dates, Ref Nos and Amount not Found in XLworksheet but which are in Text file
NOTE :Dates could be different or same, Ref No will always be different, Amounts could be Same or Different

Structure of text file

29-08-2022
Reference Number N485
Amount 38,104.00

30-08-2022
Reference Number I076
Amount 1,13,104.00

05-09-2022
Reference No I194
Amount 13,284.00

05-09-2022
Reference No I683
Amount 3,000.00

05-09-2022
Reference No I421
Amount 6,500.00

Structure of XL worksheet

Date	Reference No	Amount
29-08-2022	N485	38104.00
30-08-2022	I076	113104.00

Desired output in Msgbox
Following Records Not found in Sheet1

05-09-2022
Reference No I194
Amount 13,284.00

05-09-2022
Reference No I683
Amount 3,000.00

05-09-2022
Reference No I421
Amount 6,500.00

Thanks
NimishK

 

[portews]

Try this

Private Sub cmdNonMatchRecordsOfTextFile_Click()

Dim txtFileName As String
Dim strDataDate As String
Dim strDataTxtFile As String
Dim strDataAmt As String
Dim strSpace As String
Dim msg As String
Dim x As Variant
Dim rng As Range

txtFileName = "C:\Matching\Records.txt"

Open txtFileName For Input As #1
While Not EOF(1)
    'input 4 lines from the text file
    Line Input #1, strDataDate
    Line Input #1, strDataTxtFile
    Line Input #1, strDataAmt
'depending on the text file format, this may error on the last one, so we'll skip it if it does
On Error Resume Next
    Line Input #1, strSpace
'reset the error handler
On Error GoTo 0
    'check to see if the Reference is in the right variable
    If InStr(strDataTxtFile, "Reference") > 0 Then
        'get the reference number alone
        FindStr = Trim(Right(strDataTxtFile, Len(strDataTxtFile) - InStrRev(strDataTxtFile, " ")))
        'set where you're searching
        With Sheets("Sheet1").Range("B:B")
            Set rng = .Find(What:=FindStr, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            'if you get no match on the reference number
            If rng Is Nothing Then
                'build the non match record
                msg = msg & vbNewLine & _
                strDataDate & vbNewLine & _
                strDataTxtFile & vbNewLine & _
                strDataAmt & vbNewLine
            End If
        End With
    Else
        'if the reference isn't found where it should be, pop message box
        x = MsgBox("Data formatting error.  Please review text file.", vbCritical)
        End
    End If
Wend
'when done, close the text file
Close #1
'pop message box with results
If Len(msg) > 0 Then
    x = MsgBox("Following Records Not found in Sheet1:" & vbNewLine & msg)
Else
    x = MsgBox("No missing records")
End If
End Sub

 

[NimishK]

Bill (Portews)

Really appreciate your working. With your coding i get the following results

29-08-2022
Reference Number N485
Amount 38,104.00

05-09-2022
Reference No I194
Amount 13,284.00

05-09-2022
Reference No I683
Amount 3,000.00

05-09-2022
Reference No I421
Amount 6,500.00

I don't understand why the First Record is Displayed although the First Record is there in the Sheet. I am only interested in getting the Non-Matched records as below

05-09-2022
Reference No I194
Amount 13,284.00

05-09-2022
Reference No I683
Amount 3,000.00

05-09-2022
Reference No I421
Amount 6,500.00

Thanks
NimishK

 

[portews]

Just to verify, your data is on Sheet1, starting in A1 as shown? I'm specifically looking in the B column on Sheet1 for a match.

I'm not seeing the same problem with that code.

I started testing with "Sheet11" by mistake and I did get the list you got. That drove me crazy until I found I was on the wrong sheet.

I suggest stepping through the code and seeing what the first value of FindStr is and see if it matches what's in the text file. Check for extra spaces after the number in both the FindStr and the text file.