Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    unique write to file (excel97+)

    Hi all,

    In a large spreadsheet I need to compare 2 columns--eg column A and column B (the columns are unsorted) . I want to find unique entries in column A which do not exist in column B and export the unique words (each unique word shall be exported only once-Column A can have same unique word in many rows) to a text file named e.g c:temp|new_items.txt. Each new item shall appear on a new row in new_items.txt. IF new_items.txt exists data shall be appended to it if not,- items_txt shall be created.
    P.S Column B always has more entries than column A--Don't think that this is relevent however.....
    Thanx
    Smbs

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unique write to file (excel97+)

    This code should build the list of unique entries in column A of Sheet2 then output that list to C:Temp|New_Items.txt.

    <pre>Public Sub FindUnique()
    Dim I As Long, J As Long, K As Long, iFile As Integer
    Dim lAMax As Long, lBMax As Long
    With Worksheets("Sheet1")
    lAMax = .Range("A65536").End(xlUp).Row - 1
    lBMax = .Range("B65536").End(xlUp).Row - 1
    For I = 0 To lAMax
    For J = 0 To K
    If .Range("A1").Offset(I, 0).Value = Worksheets("Sheet2").Range("A1").Offset(J, 0).Value Then Exit For
    Next J
    If J > K Then
    For J = 0 To lBMax
    If .Range("A1").Offset(I, 0).Value = .Range("B1").Offset(J, 0).Value Then Exit For
    Next J
    If J > lBMax Then
    Worksheets("Sheet2").Range("A1").Offset(K, 0).Value = .Range("A1").Offset(I, 0).Value
    K = K + 1
    End If
    End If
    Next I
    End With
    If K > 0 Then
    iFile = FreeFile()
    Open "C:TempNew_Items.txt" For Output As #iFile
    For I = 0 To K
    Print #iFile, Worksheets("Sheet2").Range("A1").Offset(I, 0).Value
    Next I
    Close #iFile
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unique write to file (excel97+)

    Great I learn all the time!!
    By the way I love your outfit ----It sure does suit the occassion!!
    Seasons Greetings
    Smbs

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unique write to file (excel97+)

    Legare!! Sorry for the trouble but could u put a few comments in your code --there r a few things which I just can't seem to grasp!!
    My brain ain't what it used to be!!!
    Thanx
    Smbs

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unique write to file (excel97+)

    Rather than my trying to guess what you don't understand, why don't you ask about what you don't understand.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unique write to file (excel97+)

    Thanx after adding a few "watches" I managed to understand---Just brilliant---how do u do it!!!
    Smbs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •