VLOOKUP from Notepad to Excel (Two sheets)

[chig2901]

Hi, I have been looking at VLOOKUP threads across the forum and could not find specific to my needs. I tried to adjust the code elsewhere to my needs, but I am pathetically unsuccessful. I hope somebody could please help me here. Thanks.

I have a notepad file (I could make it excel also if anybody recommends for ease of code) which will have some store names on each line.

I have an excel file that has two sheets. Sheet1 has two columns. First column is store name and second is Revenues. Sheet2 is empty and that is where I want to fill the Sheet1's first and second column but only if sheet1's first column store name is in notepad. Basically notepad is my driving force and then I go to Sheet1 for VLOOKUP. if it exists only then I put in Sheet2.

This is the code I have written until now. Opening notepad, storing in array all works well. Only VLOOKUP is where I need help.

Sub test()


    Dim myFile          As String
    Dim textline        As String
    Dim myArray(200)    As Variant
    Dim x               As Long
    Dim y               As Long
    Dim z               As Long
    Dim activesheetname As String
    Dim sheet1sheetname  As String
    Dim srchRange As Range


    myFile = "C:\Desktop\master store.txt"
    x = 0
    activesheetname = ActiveSheet.Name
    sheet1sheetname = "Sheet1"
    
    Open myFile For Input As #1 
    
    Do Until EOF(1)
        Line Input #1 , textline
        myArray(x) = textline
        x = x + 1
    Loop
    
    Close #1 
    
    For y = 0 To x
        z = y + 1
        
        Worksheets(activesheetname).Range("A" & z).Value = myArray(y)
        
        'Code for VLOOKUP here
        
    Next y
    
End Sub

 

[Leith Ross]

Hello chig2901,

Can you attach a picture of the text in the file and post some data formatted the same way as it appears in the text file (spaces, tabs, etc.)?

The best option would be to post the text file to a file sharing site and post a link to file.

 

[chig2901]

This is how notepad would look like:

VICTORIA CORP.
BROADWAY DRUG INC.
PHARMACY INC.
AVENUE PHARMACY INC.
v PHARMACY INC

excel sheet1:

Customer Name	Gross Sales
BROADWAY DRUG INC.	$14.81
AVENUE PHARMACY INC.	$64.56
PHARMACY INC.	$27.63
v PHARMACY INC	$861.16
VICTORIA CORP.	$734.63

<tbody>
</tbody>

 

[chig2901]

Hi Leith Ross, my workplace computer do not allow me to use file sharing sites. Sorry for that. Would you absolutely need it?

 

[chig2901]

Hi Leith Ross or anybody who has time, can you please reply. Thank you very much.

 

[Leith Ross]

Hello chig2901,

Thanks for the example. I still have a few questions about the Notepad file.

• Is the line "excel sheet 1:" part of the file or did you add that in for clarity?
• Is there an empty line between each entry?
• It appears this file uses fixed widths. Is the example a true copy from the file?
• How does the file mark the end of the data?

 

[chig2901]

Hi Leith, thanks. My response here:

1. Is the line "excel sheet 1:" part of the file or did you add that in for clarity? [chig2901] that is actually the indicator that below data is now for excel sheet1. Notepad data ended before it. Sorry for the confusion.
2. Is there an empty line between each entry? [chig2901] No empty line in excel. Not sure why it showed like that. Sorry again.
3. It appears this file uses fixed widths. Is the example a true copy from the file? [chig2901] yes this is very real example.
4. How does the file mark the end of the data? [chig2901] there is no marking. In case of notepad, end of file. In case of excel, empty row would mark end of data.

basically, it shows both notepad data and excel sheet 1 data. it is possible that notepad could have some stores that excel may or may not have. Similarly vice-versa. but As I said earlier, notepad drives the process.

 

[Leith Ross]

Hello chig2901,

This macro will copy the data from columns "A:B" on "Sheet1" to "Sheet2" if the name in column "A" matches a name in the Notepad file.

Sub CopyStoreData()


    Dim Cell    As Range
    Dim DstRng  As Range
    Dim EndRng  As Range
    Dim File    As String
    Dim SrcRng  As Range
    Dim Stores  As New Collection
    Dim Text    As String
    Dim Wks1    As Worksheet
    Dim Wks2    As Worksheet
    
        File = "C:\Desktop\master store.txt"
        
        Set Wks1 = Worksheets("Sheet1")
        Set Wks2 = Worksheets("Sheet2")
        
        Set SrcRng = Wks1.Range("A1")
        Set RngEnd = Wks1.Cells(Rows.Count, SrcRng.Column).End(xlUp)
        Set SrcRng = SrcRng.Resize(RngEnd.Row - SrcRng.Row + 1)
        
        Set DstRng = Wks2.Range("A1")
        
        Application.ScreenUpdating = False
        
            Open File For Input As #1 
                While Not EOF(1)
                    Line Input #1 , Text
                    On Error Resume Next
                        Text = Trim(Text)
                        Stores.Add Item:=True, Key:=Text
                    On Error GoTo 0
                Wend
            Close #1 
            
            For Each Cell In SrcRng
                On Error Resume Next
                    If Stores(Trim(Cell.Value)) Then
                        Cell.Resize(1, 2).Copy DstRng
                        Set DstRng = DstRng.Offset(1, 0)
                    End If
                On Error GoTo 0
            Next Cell
        
        Application.ScreenUpdating = True
        
End Sub

 

[chig2901]

This is cool and working. Thanks a lot Leith Ross.

3 questions:

1. I am seeing that you used "RngEnd" but declared as "EndRng". and it is working. correcting it fails. Not understanding why?

2. what if I had one more column before column A in Sheet1 (that would make column A effectively as column B). How to copy it? This code does not accept negative value.

Cell.Resize(1, 2).Copy DstRng

so changing it to Cell.Resize(-1, 3).Copy DstRng does not work.

So how to copy columns before that search column.

3. I have other columns after column B but who are formulas. It does not copy them. In output on sheet2 it says #REF . so how to copy the values of the formulas.

Thanks Again.

 

[chig2901]

Hello Leith Ross or MrExcel team, can somebody please respond. Thanks a lot.