VBA Macro to create Word & Phrase Frequency

[Akuini]

@CarolynV, welcome to MrExcel.

However, is there a way to limit it from running into the next cell in the column when counting occurences of phrases?

Run this following macro "Sub add_end_of_line".

Sub add_end_of_line()
'this will add a dot at the end of every cell value.
Dim va
Dim i As Long
va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For i = 1 To UBound(va, 1)
    va(i, 1) = va(i, 1) & "."
Next
Range("A1").Resize(UBound(va, 1), 1) = va
End Sub

It will append a dot to the end of each cell value, thereby restricting a phrase to include only the value from a single cell.
After that, run "Sub Word_Phrase_Frequency_v1".

 

[Akuini]

@CarolynV
Using your example, here's the result:

dhee - Macro to create Word & Phrase Frequency 1.xlsm
	A	B	C	D	E	F	G
1	faulty replacement required.		3 WORD	COUNT		4 WORD	COUNT
2	replacement item ordered.		faulty replacement required	1		item sent to customer	1
3	Loan item sent to customer.		item sent to	1		Loan item sent to	1
4			Loan item sent	1
5			replacement item ordered	1
6			sent to customer	1
Sheet1

 

[CarolynV]

@CarolynV
Using your example, here's the result:

dhee - Macro to create Word & Phrase Frequency 1.xlsm
	A	B	C	D	E	F	G
1	faulty replacement required.		3 WORD	COUNT		4 WORD	COUNT
2	replacement item ordered.		faulty replacement required	1		item sent to customer	1
3	Loan item sent to customer.		item sent to	1		Loan item sent to	1
4			Loan item sent	1
5			replacement item ordered	1
6			sent to customer	1
Sheet1

Absolutely amazing. Thank you so much.

 

[Akuini]

Absolutely amazing. Thank you so much.

You're welcome, glad to help & thanks for the feedback.

 

[JimHep1]

This code has been really useful. However, is there a way to limit it from running into the next cell in the column when counting occurences of phrases? I have a list of free text entries (60k rows), and wish to identify the most frequent 3-word and 4-word groups. At the moment, the return is skewed as the analysis includes the cell below. For example, the following cells would return the phrase "replacement required replacement".

faulty replacement required

replacement item ordered

Loan item sent to customer

I appreciate this thread is old, but it is a really useful piece of code and your assistance would be greatly appreciated.

Can't believe I never noticed it did this. I've been using this code for a couple years with some of my own mods and love it.
Easiest fix is to find this block of logic where it is building the combined string of all phrases and put a delimiter in there in place of the space.
Basically, replace " " with "|" (see bolded logic below)

If j < 65000 Then
txa = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), "|")
Else
For i = 1 To j Step 65000
txa = txa & Join(Application.Transpose(Range("A" & i).Resize(65000)), "|") & "|"
Next
End If