Tag cloud with exceptions and progress indicator

I have changed how I want to go about doing this. Progress indicator is no longer required. See this post.

Hi there,

I am wanting to count and create a tag cloud for unique keywords on a data set that has 20,000 complaints on seperate rows by using something like a word frequency count macro (pivot table is not necessary) and chandoos tag cloud script with the following modifcations:

1. If a word appears multiple times in one row it should only be counted once.

2. Because the data set is so large (at least 200,000 words) I would like keywords only to be displayed if they match certain user defined criteria:
a) They aren't an 'excluded keyword' such as 'and, this, that, they etc'
b) The keyword appears at least x or more times.

3. As theses script/s take a long time to run on such a large dataset I would like to include a progress indicator.

What would be the best way to do this?

Thanks so much for your help. I'm new here and if you want me to clarify anything please ask.

How about posting an example of how the data is laid out and expected results?

Please see attatched example. The macros are included but I have been unable to modify them to do what I want.

I have added comments to the spreadsheet but to be clear I want to modify the MakeWordList to:
-list a count of words in a defined area on the 'data worksheet',
-above a defined minimum word count,
-only count a word once per row and
-not count definted exceptions with
-a progress indicator.

For the createCloud macro I want to modify it to:
-Grab the data from the MakeWordList output and display it as a visually friendly tag cloud on the same spreadsheet with
-a progress indicator

I'm thinking of using a plain bar progress meter.

Read the forum rules on uploading a workbook

I have now attatched the worksheet as per forum rules.

I have been trying to modify the code myself but I can't even get past getting the first macro to grab data from the worksheet.

When I set A1 in the below code to anything other than A1 (such as B1) I get a 1004 runtime error that states the pivot table name is not valid.

WordListSheet.Range("A1") = "All Words"
WordListSheet.Range("A1").Font.Bold = True

On Debug the highlighted text is

    Set PT = PC.CreatePivotTable _
        (TableDestination:=Range("C1"), _
        TableName:="PivotTable1")

Note I don't even require a pivot table to make the cloud.

After much mucking around I am now using the code that Andy Pope posted elsewhere on this forum to count the words. I found that this script runs very quickly if the data does not have filters etc on it. (if there are filters it seems to go from 3mins to 2hrs). Consequently it is fine without progress indicators.

Sub CreateUniqueWords()
    Dim rngData As Range
    Dim rngCell As Range
    Dim colWords As Collection
    Dim vntWord As Variant
    
    On Error Resume Next
    
    Set colWords = New Collection
    Set rngData = Range("B30:B86")
    For Each rngCell In rngData.Cells
        For Each vntWord In Split(Replace(Replace(Replace(rngCell.Value, """", ""), "]", ""), "[", ""), " ")
            colWords.Add colWords.Count + 1, vntWord
            With Cells(29 + colWords(vntWord), 3)
                .Value = vntWord
                .Offset(0, 1) = .Offset(0, 1) + 1
            End With
        Next
    Next
    
    With Range("C30", Cells(Rows.Count, 3).End(xlUp)).Resize(, 2)
        .Sort .Cells(1, 2), xlDescending
    End With
    
End Sub

The main modification I would like made to this macro is to make it only count a unique word once per row. How would this be done?

To deal with exceptions I now think the best way to deal with it would be to write a macro that looks through the output word list and cuts a row that begins with a listed exception word to a different worksheet. This should not be too hard.

If you're disappointed that no one has interrupted your monologue to assist, please consider that you have never lucidly explained what you're trying to do.

Sheet Data has columns A-F with sentences in columns A, columns B:E blank, random words in column F, and an AutoFilter applied for some reason to the whole thing. What are the columns for?

Where is the list of words to extract?

Do only the extracted words appear in the output? If that's true, then a list of words to exclude is unnecessary, true?

... and many more questions like that.