Improve company productivity with a Business Account.Sign Up

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

Unique word identification in excel documents

I am looking for a macro that can take an excel spreadsheet and generate a text file containing a list of UNIQUE words used in the spreadsheet. Some spreadsheets can contain as many as 80,000 words....

Note: individual cells can have multiple words separated by spaces.

A sample input files is attached

Thank you!
OFX-2.1.1.txt
0
cyber-33
Asked:
cyber-33
  • 2
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Sorry, I'm confused by your attached file.

Is this a text file you wish to open in MS-Excel & then produce another text file containing the unique words, or is this the resultant file after the required process is complete?

Perhaps you could attach an example MS-Excel workbook instead, so that it is clear what is required.

Thanks.

BFN,

fp.
0
 
Rgonzo1971Commented:
Hi,

pls try this

The macro will ask for the file and then process it and paste the result in the active sheet

Sub macro()

    Const wdDoNotSaveChanges = 0
    
    Set wdApp = CreateObject("Word.Application")
    Dim fd As Object
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

    Set fd = wdApp.FileDialog(msoFileDialogOpen)
    With fd
        .Filters.Clear
        .Filters.Add "text (*.txt)", "*.txt"
        Result = .Show
        If Result = False Then Exit Sub
        Set wdDoc = wdApp.Documents.Open(Filename:=.SelectedItems(1))
    End With
    
    For Each aword In wdDoc.Words
        SingleWord = Trim(LCase(aword))
        If Not (Left(SingleWord, 1) < "a") And Not (Left(SingleWord, 1) > "z") Then
            If Not Dict.Exists(SingleWord) Then
                Dict.Add SingleWord, SingleWord
            End If
            
        End If
    Next
    wdDoc.Close wdDoNotSaveChanges
    WordList = Dict.Items
    ActiveSheet.Range("A1").Value = "Word List"
    For Idx = 0 To Dict.Count - 1
        s = WordList(Idx)
        ActiveSheet.Range("A1").Offset(Idx + 1).Value = WordList(Idx)
    Next
    
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(Range("A2"), Range("A2").End(xlDown)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(Range("A1"), Range("A1").End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    MsgBox "Done"
    
    Set wdApp = Nothing
    Set Dict = Nothing


End Sub

Open in new window

Regards
0
 
cyber-33Author Commented:
Thank you!
0
 
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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