[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Excel macro to search and copy to a sheet

Hi,

I need a macro which can search for all emp id's in the txt file and search in the excel if found leave as it is and cut paste the rest to a new sheet.Then sort the content to the same manner as the text file. Last think is any emp id is not found then it has to leave a blank row there.

regards
Sharath
0
bsharath
Asked:
bsharath
  • 8
  • 6
  • 3
1 Solution
 
namanpatelCommented:
Can you give me the format of the text file you have?
0
 
bsharathAuthor Commented:
Name  Age  Sex  City  Country
These are the fields i have
0
 
namanpatelCommented:
what are the separator for the fields?
you can import them in to the excel with
Data>import external data>Import data option.

If you want solution for this condition i can provide
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bsharathAuthor Commented:
Can u provide
0
 
namanpatelCommented:
Download software @

http://members.iinet.net.au/~brettdj/

thru this you can easily do you task but you need to covnert your text file to excel file.

Have u converted it?
0
 
hiteshgoldeneyeCommented:
try this
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j = 1
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).Cut Destination:=Sheet2.Rows(s2row)
             Sheet1.Rows(i).Insert(xlDown)
            s2row = s2row + 1
       End If
 Next
 FF = FreeFile
 Open "C:\filename.txt" For Input As #FF
s1row=2
 While Not EOF(FF)
      Line Input #FF, str1
      For i = 1 To Sheet1.UsedRange.Rows.Count
           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                Sheet1.Rows(s1row).Copy Destination:=Sheet2.Rows(555)
                Sheet1.Rows(i).Copy Destination:= Sheet1.Rows(s1row)
                Sheet2.Rows(555).Copy Destination:= Sheet1.Rows(i)
                s1row = s1row + 1
            End If
      Next
Wend
 Close FF

End Sub


0
 
bsharathAuthor Commented:
hitesh
This is not searching and coping to other sheet.

Patel downloaded the addon.But how should i select for this requirement.
This link is of good help...
0
 
hiteshgoldeneyeCommented:
is ur id in first column or some other column
0
 
bsharathAuthor Commented:
Its in some other colum.
0
 
hiteshgoldeneyeCommented:
in that case change the value of j as per your requirement
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 1
j = 1  'Change the column no here
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).Cut Destination:=Sheet2.Rows(s2row)
             Sheet1.Rows(i).Insert(xlDown)
            s2row = s2row + 1
       End If
 Next
 FF = FreeFile
 Open "C:\filename.txt" For Input As #FF
s1row=2
 While Not EOF(FF)
      Line Input #FF, str1
      For i = 1 To Sheet1.UsedRange.Rows.Count
           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                Sheet1.Rows(s1row).Copy Destination:=Sheet2.Rows(555)
                Sheet1.Rows(i).Copy Destination:= Sheet1.Rows(s1row)
                Sheet2.Rows(555).Copy Destination:= Sheet1.Rows(i)
                s1row = s1row + 1
            End If
      Next
Wend
 Close FF

End Sub
0
 
bsharathAuthor Commented:
I have emp id
100
101
103
in the text file
And all the same emp id's in the sheet1 on the excel.When i run the macro only the header it cut copied to sheet 2 and something runs and all the rows go dow to 300th row and no results.
0
 
hiteshgoldeneyeCommented:
can you upload the file in 2003 format
0
 
bsharathAuthor Commented:
File uploaded
http://www.filepatio.com/3646
I have these text in txt file
107
121
127

See sheet 2 to see headers
0
 
hiteshgoldeneyeCommented:
it is in 2007 format can you upload in 2003 format?
0
 
bsharathAuthor Commented:
0
 
hiteshgoldeneyeCommented:
Here you go
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 2
j = 1  'Change the column no here

  For i = 1 To 13 + Sheet1.UsedRange.Rows.Count
           FF = FreeFile
           Open "C:\filename.txt" For Input As #FF
           Do While Not EOF(FF)
                Line Input #FF, str1
                idfound = False
               If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                idfound = True
                Exit Do
              End If
            Loop
           Close FF
 
  If idfound = False And Sheet1.Cells(i, j) <> "" Then
             Sheet1.Rows(i).Cut Destination:=Sheet2.Rows(s2row)
             Sheet1.Rows(i).Insert (xlDown)
            s2row = s2row + 1
  End If


     
 Next
 FF = FreeFile
 Open "C:\filename.txt" For Input As #FF
s1row = 13
 While Not EOF(FF)
      Line Input #FF, str1
      For i = 1 To Sheet1.UsedRange.Rows.Count

           If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then
                Sheet1.Rows(s1row).Copy Destination:=Sheet2.Rows(55555)
                Sheet1.Rows(i).Copy Destination:=Sheet1.Rows(s1row)
                Sheet2.Rows(55555).Cut Destination:=Sheet1.Rows(i)
                s1row = s1row + 1
            End If
      Next
Wend
 Close FF

End Sub


0
 
bsharathAuthor Commented:
Hitesh can this only be done for emp id's
Cant we do for Machine names or Names?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now