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

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

Sort excel sheet as per the text file.

Hi,

I have some names in txt file need to sort the same order as the excel file.Data is in an excel sheet.If any name is not found in the excel sheet the leave a row blank as per the txt file.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 5
  • 2
  • 2
  • +1
1 Solution
 
weellioCommented:
can you provide more information?
a few example lines form the text file and a few example lines form the excel file,...

or uploade a sample text and excel file and say specifically what you want accomplished.

0
 
bsharathAuthor Commented:
In the text file i have

Sharath
Ramesh
Sureshr
      Blank space
Mahesh

Excel I have
Ramesh
Suresh
Mahesh
Sathish
Sharath
Output should be as in the excel
Sharath
Ramesh
Sureshr
      Blank space (As the text file has a blank so the data in the excel the whole row should be blank)
Mahesh
Sathish  (This name is not there in the txt file so all names which are not there in the txt file has to be made colored.)

Sorry if i have not mentioned.There would be data in the start and end of the names.For ex:
EMP ID  Name  Email ID                     Age   Sex
1536   Sharath   Sharat@plc.com   30   M
So when the excel sorts as per the txt file all the contents next to sharath has to be sorted as well.

THANKS
Sharath
0
 
hiteshgoldeneyeCommented:
'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 = 1
 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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsharathAuthor Commented:
Hitesh is this the same macro as you gave me befor.This does not sort blanks according to the txt file.
0
 
hiteshgoldeneyeCommented:
'yes it is the same i guess because the questions are basically the same.
'let me know if this one works fine
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


0
 
dirknibleckCommented:
I don't get this one. You want it sorted according to the Excel file, but somehow you want the blanks to remain where they are in the text file?
0
 
bsharathAuthor Commented:
You Have given me a way previously to do this with a excel sheet.I want the same thing for txt file.

Sort the txt file as per the excel sheet.If the excel has a blank row then leave a blank line in the txt file.
0
 
bsharathAuthor Commented:
ANy help on this excel Q...

Any of you experts can help.Very urgent please...
0
 
dirknibleckCommented:
This should do it, I think.

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.Columns("C").Insert
   
    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").Delete
    ws2.Columns("B").ClearContents
   
    MsgBox "Done like dinner."
   
End Sub
0
 
bsharathAuthor Commented:
Sorry forgot the link.

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_22766614.html

Need this very urgent experts...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now