Need to sort the excel as per my txt file

Sorting in excel will usually put the blanks at the end...
My suggestion would be that you try to rather put numbers in front of each row, and sort...

Do you need to do this using VB ?

To do manually I would ;

Import the text file into excel,  in the next blank column to the right of the imported file,  add a consecutive number - lets call the column 'Sort Key' ( 1,2,3 etc ).

In the next column use a VLOOKUP to check for records which are included in your text file, but are not in your excel file ( i.e. VLOOKUP returns - "#n/a" ).
Copy the 'Sort Key' records found and paste into a new column BELOW the last row of your existing excel records.  

Populate the rest of this column with a VLOOKUP returning the column you created earlier in your text file.

Sort the excel file by the 'Sort Key' you have created.

Quite difficult to explain - if you describe your data in more detail I can give more specific help.


d.

Dan_Wong
Can you help me with a macro please

bsharath - I'd need further details of how you would like this to work.  

Preferably examples if possible ? You can upload files at http://www.ee-stuff.com


Dan

Uploaded the file
https://filedb.experts-exchange.com/incoming/ee-stuff/4213-Book12.txt 

Please change the file's txt ext to xls

Do you have an example of the text file also ?

Also - will there ever be a situation where ;
There is an Emp ID contained in your s/sheet but NOT in the text file ?

Is the Emp ID always unique in both the text file and Excel sheet ?


If so how do you want this dealt with ?


d.

Yes there are chances of 1 emp id not there either in txt or excel file

The text file is just no's like this

1234
1233
1222
121
2234

If there is a emp id in the txt file and not there in the excel i want to leave a blank row in the excel.
If there is a emp id that's there in the excel and not there in the txt file .Then need to send it to the end of the sheet and make it bold or color it.

Any  help....

Sorry mate - I've got a couple nightmares at work at the moment - I'll try to get back to this as soon as I can.

Anyone else's assistance would be welcome ...

Hi bsharath - sorry for the delay.

The below macro should work - you may need to adjust slightly to fit though...
The more I think about this - the more I think you should possibly attempt to do this in some other manner  - is there no other possible way to set up your spreadsheet for example ?

I've made lot's of assumptions in writing this.

Anyway try this..

Public Sub Sort()

'Import text file to excel
'Assumes text file is in same folder as this workbook
'Assumes text file is the only text file in this folder
'Assumes text file contains no header

DIRECTORY = Replace(ActiveWorkbook.FullName, ActiveWorkbook.Name, "")

Dim i As Variant
Dim fs As Object
Set fs = Application.FileSearch

With fs
    .LookIn = DIRECTORY
    .Filename = "*.txt"
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
            Workbooks.OpenText (.FoundFiles(i))
        Next i
    Else
        MsgBox "Text file not found.", vbExclamation
    End If

Txtrange = ActiveSheet.Range("A:A")
No_of_Records_txt = Application.WorksheetFunction.CountA(Txtrange)
Lookup_Range = ActiveSheet.Name

For A = 1 To No_of_Records_txt
ActiveWorkbook.ActiveSheet.Range("B" & A).Value = A
Next A

XlsRange = ThisWorkbook.ActiveSheet.Range("B:B")
no_of_records_Xls = Application.WorksheetFunction.CountA(XlsRange)

For b = 2 To no_of_records_Xls
ThisWorkbook.ActiveSheet.Range("Z" & b).Value = "=Vlookup(B" & b & "," & Lookup_Range & ".txt!A:B,2,false)"
If IsError(ThisWorkbook.ActiveSheet.Range("Z" & b).Value) Then
ThisWorkbook.ActiveSheet.Range("A" & b).Font.Bold = True
End If
Next b

ThisWorkbook.ActiveSheet.Range("1:" & no_of_records_Xls).Sort Key1:=ThisWorkbook.ActiveSheet.Range("Z1"), Header:=xlYes

For c = 2 To no_of_records_Xls
    If ThisWorkbook.ActiveSheet.Range("Z" & c).Value <> c - 1 Then
        ThisWorkbook.ActiveSheet.Range("Z" & c).EntireRow.Insert
        No_of_records = No_of_records + 1
        c = c + 1
    End If
Next c

ActiveWorkbook.Close False
ThisWorkbook.ActiveSheet.Range("Z:Z").Clear


End With

End Sub


d.

I get an error here

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

Expected End Sub
---------------------------
OK   Help  
---------------------------


ThisWorkbook.ActiveSheet.Range("Z:Z").Clear

Hi bsharath - have you definitely copied the full text ?

The first line should be  - Public Sub Sort()
The last line should be  - END Sub

d.

Debug error here

Set fs = Application.FileSearch

I have put both the excel and the txt file in the same folder.I have copied the whole macro.

I get Run time error 445

Can you upload excel and text files you are working with ?

Also can you check your references ;
In the Excel Visual Basic window - > TOOLS - > REFERENCES

What's showing as marked ?

'let me know if this works for you if not can you upload ur excel file at filepatio in '.xls format
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

Sorting runs great but when there is a empty line in txt file the excel has to leave a blank in the excel.

'try this
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
      if str1 = "" Then
            Sheet1.Rows(s1row).Insert (xlDown)
      Else
         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
     End if
Wend
 Close FF

End Sub

No this does not work..

If i want to show a particular colum to sort.What should i do.

can you upload your file in .xls format

'try this
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 2

j = Val(InputBox("Enter Column No"))  'Take input from user

  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
      if str1 = "" Then
            Sheet1.Rows(s1row).Insert (xlDown)
      Else
         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
     End if
Wend
 Close FF

End Sub

I have uploaded the file.
http://www.filepatio.com/5093


I have this data in txt file.

Dev-chen-pc2064
Dev-chen-pc1178
Dev-chen-pc2068
Dev-chen-pc2542
                                       This line is empty So i want the excel to sort the same way and leave a row.
Dev-chen-pc2156
Dev-chen-pc2271
Dev-chen-pc2040
Dev-chen-pc2056

'This one works fine for me with your worksheet
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 2

j = Val(InputBox("Enter Column No"))  'Take input from user

  For i = 1 To 13 + Sheet1.UsedRange.Rows.Count
           FF = FreeFile
           Open "C:\mc.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:\mc.txt" For Input As #FF
s1row = 13
 While Not EOF(FF)
      Line Input #FF, str1
      If Trim(str1) = "" Then
            Sheet1.Rows(s1row).Insert (xlDown)
      Else
         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
     End If
Wend
 Close FF

End Sub


' the result i get is

Dev-chen-pc2064
Dev-chen-pc1178
Dev-chen-pc2068
Dev-chen-pc2542
Dev-chen-pc2156

Dev-chen-pc2271
Dev-chen-pc2040
Dev-chen-pc2056

Here you go Sharath. This should do what you are looking for.


Sub sortEmps()

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim ws2 As Worksheet
    Dim file As String
    Dim endrow As Long
   
    file = Application.GetOpenFilename(, , "Choose text file to open")
   
    If file = "False" Then
        MsgBox "Operation Cancelled"
        Exit Sub
    End If
   
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
   
    Set wb = Application.Workbooks.Open(file)
    wb.Worksheets(1).Cells.Copy
   
    ws2.Range("A1").PasteSpecial
   
    wb.Close False
   
    endrow = ws2.Range("A65536").End(xlUp).Row
   
    For i = 1 To endrow
        ws2.Cells(i, 2) = i
    Next i
   
    endrow = ws.Range("A65536").End(xlUp).Row
   
    ws.Range("c1").Formula = "=IF(ISNA(VLOOKUP(b1, Sheet2!A:B, 2, FALSE)), 65537, VLOOKUP(b1, Sheet2!A:B, 2, FALSE))"
   
    ws.Range("c1").Copy
    ws.Range("c1:c" & endrow).PasteSpecial xlPasteFormulas
   
    ws.Cells.Sort ws.Range("c1"), xlAscending
   
    endrow = ws2.Range("A65536").End(xlUp).Row
   
    For i = 1 To endrow
        If ws.Cells(i, 3) <> i Then
            ws.Rows(i).Insert
        End If
    Next i
   
    ws.Columns("c").ClearContents
    ws2.Columns("B").ClearContents
   
    MsgBox "Done like dinner."
   
End Sub

hey sharath
'This one works fine for me with your worksheet
' tell me what result you get, and how is it different from what you expect
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 2

j = Val(InputBox("Enter Column No"))  'Take input from user

  For i = 1 To 13 + Sheet1.UsedRange.Rows.Count
           FF = FreeFile
           Open "C:\mc.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:\mc.txt" For Input As #FF
s1row = 13
 While Not EOF(FF)
      Line Input #FF, str1
      If Trim(str1) = "" Then
            Sheet1.Rows(s1row).Insert (xlDown)
      Else
         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
     End If
Wend
 Close FF

End Sub

Hitesh

I have this in the txt file

Sharath

Reddy

ramesh

And have this in the xecel
Sharath
Reddy
ramesh

When i run the macro

This data
Sharath
Reddy
ramesh
Comes to the 13th row and that's it nothing else happens...

'Ok try this one
Sub Macro1()
Dim FF As Integer, str1 As String, j As Integer, idfound As Boolean
s2row = 2

j = Val(InputBox("Enter Column No"))  'Take input from user

  For i = 1 To  Sheet1.UsedRange.Rows.Count
           FF = FreeFile
           Open "C:\mc.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:\mc.txt" For Input As #FF
s1row = 1
 While Not EOF(FF)
      Line Input #FF, str1
      If Trim(str1) = "" Then
            Sheet1.Rows(s1row).Insert (xlDown)
      Else
         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
     End If
Wend
 Close FF

End Sub

Now it is different.
I have
Sharath
reddy
Ramesh in the txt file

And
Sharath
Ramesh
Reddy in excel
when i run the macro it just deletes ramesh and does not sort as text file.In the text file after each word i have a blank line....

Any Help....

I get a debug error. here

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

What is the error that you get, what are you entering in the InputBox?

I dont get an error.

If a data in the txt file is not found in the excel then need to mention it or leave the line blank

Can you upload the file in .xls format?

I have this in the txt file.

Ganesh
Mahesh
Ramesh
Sharath

Sharathh
sharath reddy

Naresh

Spaces as per above.I have uploaded the file here.
http://www.filepatio.com/7030

I get run time error. 1004

When debugged.
If UCase(str1) = UCase(Sheet1.Cells(i, j)) And Sheet1.Cells(i, j) <> "" Then

but i tested this macro it runs fine, can you tell me what are you entering in the inputbox

AS the data is in Colums "A".I enter A and 1 also. For both get the same error.

please recopy and paste the code, its working perfectly here