How to Creating a Unique Keyword List?

[John Caines]

Dear Forum members,

Can anyone help me?
What I'm trying to achieve is the following;
I have a big keyword list saved in Excel.
Something like the following,
These are all keyword phrases;

car rent
car hire
cars for rent uk
etc etc etc

All listed in Column A

All phrases in Column A, and in separate rows.(1 phrase per row.)about 2000 lines (Rows) in total.
What I want to know is there any way of selecting the whole list and exporting it (To save it as another list. A list of just unique keywords??

So, It would create a list like;
car
rent
uk
hire
for
etc etc etc.

So basically I want excell to look at all the words and export them to another list showing just unique keywords, 1 per line.

This is so I can see from a huge list what all the unique keywords are.
Is there a way of doing this within Excel Now?? or has someone made a plugin ( Macro) to achieve this??
I've looked at the sort & filter options, but it doesn't appear to have this function?

Any thoughts on this would be great.
I've wanted to do this for ages and I'm a basic beginner in excel and just can't work it out.

Hope someone can help.
Many Thanks
John
Many Thanks
John

 

[jindon]

OK
Please enter following formula in any cell, but not in the cell C14

=Code(C14)&":"&Len(C14)

And get back to me with the result

thanks

 

[John Caines]

reply

Hi Jindon.
I done what you said.
I typed in the code in cell D20 (Nothing was in it,, and a light blue line box appeared around Cell C14????
I clicked enter and now in Cell D20 it has a very small green triangle in the top left hand corner of the cell with #VALUE!


Any Ideas??
Means nothing to me??

Cheers.
I'm still awake,
Hope you can sort it.

I can always email you the sheet if you can accept it.
But if I remember right, where you are working, you can't??

Many Thanks
John Caines

 

[jindon]

1) did you use the Pattern I last posted?
2) Are the data inported from another application or websites?

 

[John Caines]

Late reply

Sorry for the late reply Jindon.

I wrote you a large reply, with pictures etc,,,, and my connection went down. Couldn't save what I'd written either(

I'll start again.

Ok. the pattern I've used is this; (Which was your last post;
=============================================================
.Pattern = "\b([a-z0-9:\;&\+-/\|\\]{1}|200|by|do|fe|ga|not|we|from|get|theat|with|a(ll|m|n|nd|ny|t)|c(a|o|om)|o(f|n|r)|i(f|s|t)|m(e|y)|e(a|d|n)|hi|i(d|l|v)|the|for|(g|t)o|in|up|you(r)?(rs)?|l(ike|ook))\b"
============================================================

I've created a template which I've tried the macro with and still tried in the old workbook.
Still an error.
I've tried importing a large keyword list Jindon from an ordinary text file which has 4,453 phrases.
I've searched for the word "web" (Without quotation marks)

On both sheets I get the same results.
In Cell C14 --- which is just empty. But in Cell E 14 it says (Whatever it's meant to be has 588 occurances).


Also as a note, in cell C293,, there is the number 24 which has 4 occurances.
I did a "find 24" in Col A and found a phrase in row 1188, which was ;
"asian web site design ecommerce 24" (Again without quotation marks.

I'll attach a couple of photos.

Maybe if somehow you could tell me how to just search in Col A (IE all the phrases) for something that occurs 588 times. Then we will know what it is??? A word ?? A number??


Many thanks again Jindon.
John Caines

==================================

New Microsoft Excel Worksheet.xls
	A	B	C	D	E	F	G
290	asianbuilderdesignfreesitesitewebweb		sample		4		0.02%
291	asianbuilderdesignpagesoftwareweb		dc		4		0.02%
292	asianbuilderdesignsitetemplateweb		premier		4		0.02%
293	asianbuilderecommercefreesiteweb		24		4		0.02%
294	asianbuilderecommercehostsiteweb		long		4		0.02%
295	asianbuilderecommercesitesoftwareweb		beach		4		0.02%
296	asianbuilderecommercesiteweb		improved		4		0.02%
297	asianbuildingdesignfreesitewebweb		ireland		4		0.02%
NicheKWresults

=======================================

New Microsoft Excel Worksheet.xls
	A	B	C	D	E	F	G	H
1	PhrasesThatInclude:web		UniqueKeywords		No.OfAppearances		%OfAppearances
2	TotalPhrases:4453		Total:305		Total:23582
3
4
5	3dflashwebdesign		web		4538		19.24%
6	3dflashwebdesignasia		design		2523		10.70%
7	3dflashwebdesignthailand		site		1966		8.34%
8	aecommercewebpagehost		hosting		1380		5.85%
9	aecommercewebpagehostasia		asian		1115		4.73%
10	aecommercewebpagehostthailand		asia		1113		4.72%
11	awebhostingwebsitedesign		thailand		1113		4.72%
12	awebhostingwebsitedesignasia		ecommerce		1035		4.39%
13	awebhostingwebsitedesignthailand		free		795		3.37%
14	aaadesignhostingsiteweb				588		2.49%
15	aaadesignhostingsitewebasia		page		452		1.92%
16	aaadesignhostingsitewebthailand		domain		412		1.75%
NicheKWresults

 

[jindon]

Ok John

Can you also post the most current code that you are using?

 

[John Caines]

code

right now I have a sheet, Im using a created button.
The VB code (Yours as No way I could write this is as follows;
=========================================
Sub Test()
Dim a, dic As Object, x, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long
myTxt = InputBox("SiamSites - Niche Keyword Finder")
If Len(myTxt) = 0 Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3)
With Sheets("AllKW")
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("VBScript.RegExp")
.Pattern = "\b([a-z0-9:\;&\+-/\|\\]{1}|200|by|do|fe|ga|not|we|from|get|theat|with|a(ll|m|n|nd|ny|t)|c(a|o|om)|o(f|n|r)|i(f|s|t)|m(e|y)|e(a|d|n)|hi|i(d|l|v)|the|for|(g|t)o|in|up|you(r)?(rs)?|l(ike|ook))\b"
.Global = True
.IgnoreCase = True
For Each e In a
If InStr(1, e, myTxt, 1) > 0 And Not dic.exists(e) Then
i = i + 1: b(i, 1) = e
x = Split(.Replace(e, ""))
If IsArray(x) Then
For Each s In x
If Not IsEmpty(s) And Not dic.exists(s) Then
n = n + 1
dic.Add s, n
End If
c(dic(s), 1) = s: c(dic(s), 3) = c(dic(s), 3) + 1
Next
Else
If Not dic.exists(e) Then
n = n + 1: dic.Add e, n
End If
c(dic(e), 1) = e: c(dic(e), 3) = c(dic(e), 3) + 1
End If
End If
Next
End With
Set dic = Nothing: Erase a
If i < 1 Then
MsgBox "Not Found"
Exit Sub
End If
Application.DisplayAlerts = False
On Error Resume Next
Sheets("NicheKWresults").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Sheets.Add.Name = "NicheKWresults"
With Sheets("NicheKWresults")
With .Range("a1")
.Resize(, 7).Value = Array("Phrases That Include: " & myTxt, "", "Unique Keywords", "", "No. Of Appearances", "", "% Of Appearances")
.Offset(4).Resize(i).Value = b
With .Offset(4, 2).Resize(n, 3)
.Value = c
.Sort key1:=.Range("c1"), order1:=xlDescending, header:=xlNo
myTotal = [sum(NicheKWresults!e:e)]
With .Offset(, 4).Resize(, 1) '<- this is Col.G
.FormulaR1C1 = "=round(rc[-2]/" & myTotal & ",4)"
.NumberFormat = "0.00 %"
End With
End With
.Offset(1).Resize(, 5).Value = Array("Total Phrases: " & i, "", "Total: " & n, "", "Total: " & myTotal)
End With
.Range("a:g").EntireColumn.AutoFit
End With
End Sub

=========================================
I just ran a different word search for the word "site"

Now, there is an empty cell in this search in C15. Whatever is meant toi be there has 224 occurances.

and again, the number "24" comes up with 4 occurances on row 158.

Hope this helps Jindon.

Many Thanks
John

 

[jindon]

Re: code

John
can you just quickly run this code?

Sub Test()
Dim a, dic As Object, x, myTxt As String, b(), c(), n As Long, i As Long, e, s, myTotal As Long
myTxt = InputBox("SiamSites - Niche Keyword Finder")
If Len(myTxt) = 0 Then Exit Sub
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
ReDim b(1 To Rows.Count, 1 To 1): ReDim c(1 To Rows.Count, 1 To 3)
With Sheets("AllKW")
    a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("VBScript.RegExp")
    .Pattern = "\b([a-z0-9:\;&\+-/\|\\]{1}|200|\d{2}|by|do|fe|ga|not|we|from|get|theat|with|a(ll|m|n|nd|ny|t)|c(a|o|om)|o(f|n|r)|i(f|s|t)|m(e|y)|e(a|d|n)|hi|i(d|l|v)|the|for|(g|t)o|in|up|you(r)?(rs)?|l(ike|ook))\b"
    .Global = True
    .IgnoreCase = True
    For Each e In a
        If InStr(1, e, myTxt, 1) > 0 Then
            i = i + 1: b(i, 1) = e
            x = Split(.Replace(Trim(e), ""))
            If IsArray(x) Then
                For Each s In x
                    If Not IsEmpty(s) And s <> " " And Not dic.exists(s) Then
                        n = n + 1
                        dic.Add s, n
                    End If
                    c(dic(s), 1) = s: c(dic(s), 3) = c(dic(s), 3) + 1
                Next
            Else
                If Not dic.exists(Trim(e)) Then
                    n = n + 1: dic.Add e, n
                End If
                c(dic(e), 1) = e: c(dic(e), 3) = c(dic(e), 3) + 1
            End If
        End If
    Next
End With
Set dic = Nothing: Erase a
If i < 1 Then
    MsgBox "Not Found"
    Exit Sub
End If
Application.DisplayAlerts = False
On Error Resume Next
Sheets("NicheKWresults").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Sheets.Add.Name = "NicheKWresults"
With Sheets("NicheKWresults")
    With .Range("a1")
        .Resize(, 7).Value = Array("Phrases That Include: " & myTxt, "", "Unique Keywords", "", "No. Of Appearances", "", "% Of Appearances")
        .Offset(4).Resize(i).Value = b
        With .Offset(4, 2).Resize(n, 3)
            .Value = c
            .Sort key1:=.Range("c1"), order1:=xlDescending, header:=xlNo
            myTotal = [sum(NicheKWresults!e:e)]
            With .Offset(, 4).Resize(, 1) '<- this is Col.G
                .FormulaR1C1 = "=round(rc[-2]/" & myTotal & ",4)"
                .NumberFormat = "0.00 %"
            End With
        End With
        .Offset(1).Resize(, 5).Value = Array("Total Phrases: " & i, "", "Total: " & n, "", "Total: " & myTotal)
    End With
    .Range("a:g").EntireColumn.AutoFit
End With
End Sub

 

[John Caines]

got an erro Jindon

this comes up as an error in VB;

If NotIsEmpty(s) And s <> " " And Not dic.exists(s) Then


Also the top line is in yellow "Sub Test()

 

[John Caines]

reply

I just checked it to the old code and put a space in it Jindon.

So the line was;
If Not IsEmpty(s) And s <> " " And Not dic.exists(s) Then


It worked.
But,, same problem.

With the word "web" still blank in cell C14 but now 592 occurances?????
Strange??

 

[jindon]

can you add on space between Not & IsEmpty

If Not IsEmpty(s) And s <> " " And Not dic.exists(s) Then