?
Solved

Excel search a file and delete all data not found

Posted on 2007-07-27
18
Medium Priority
?
208 Views
Last Modified: 2010-03-05
Hi,

I have a text file in which i have Employee id's and have a excel which has all the data about the employee.
What i want to do now is a macro which will search the text file for all the emp no's and leave the rows if found and delete any emp id's not found.

Regards
Sharath
0
Comment
Question by:bsharath
18 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 19580890
Would it be OK to do this in Excel and then export the result back to a text file?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 19580891
What format is the text file? Does it just contain the EmployeeIDs and nothing else?
0
 
LVL 11

Author Comment

by:bsharath
ID: 19581325
its txt format and i have only emp id's
like
C010
1983
127
123
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Author Comment

by:bsharath
ID: 19581327
Excel to excel is ok for me...
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19583673
This macro will delete the ones which are not found in the text file, let me know if any problem

Sub Macro1()
Dim FF As Integer, str1 As String, j as Integer, idfound as Boolean

j=1   ' Assuming emp id's are in column A
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = false
       While Not EOF(FF)
           Line Input #FF, str1

           If  UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = true
            End If

        Wend
       if idfound = false
                Sheet1.Rows(i).Delete
       End if
 Next
Close FF
End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19583806
I get this.

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help  
---------------------------
In this place
       if idfound = false
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19583812
sorry forgot a then
Sub Macro1()
Dim FF As Integer, str1 As String, j as Integer, idfound as Boolean

j=1   ' Assuming emp id's are in column A
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = false
       While Not EOF(FF)
           Line Input #FF, str1

           If  UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = true
            End If

        Wend
       if idfound = false then
                Sheet1.Rows(i).Delete
       End if
 Next
Close FF
End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19583850
I have the emp id's in different colum
Any way for it to ask me which colum the emp id's are there
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19583860
Sub Macro1()
Dim FF As Integer, str1 As String, j as Integer, idfound as Boolean

j=Val(InputBox("Column No"))   ' Enter column no
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = false
       While Not EOF(FF)
           Line Input #FF, str1

           If  UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = true
            End If

        Wend
       if idfound = false then
                Sheet1.Rows(i).Delete
       End if
 Next
Close FF
End Sub
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590789
hi did u try it?
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590805
Should i have data in Sheet 1 and sheet 2 and then run the macro
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590885
yes of course data should be there in sheet1
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590925
I get this.

Run time error'1004'
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19590945
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j=Val(InputBox("Column No"))   ' Enter column no
For i = 1 To Sheet1.UsedRange.Rows.Count
      idfound = False
      FF = FreeFile
      Open "C:\filename.txt" For Input As #FF

       While Not EOF(FF)
           Line Input #FF, str1
           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = True
            End If
        Wend
        Close FF
       If idfound = False Then
             Sheet1.Rows(i).DELETE
       End If
 Next

End Sub
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590979
Run time error 1004
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19591262
Are you sure try again, i have tested this macro
0
 
LVL 11

Author Comment

by:bsharath
ID: 19591284
Application defined or object defined error.

I get this error.

Checked it again.Same error.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19591398
you have to enter a column no in the inputbox
e.g 1
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question