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]

Re: it's making sense now

But does all this have to be on 1 line Jindon?? Or can you use a comma or something and make 2 or 3 lines?? As the line of code with these words to be omitted will be very long??

haven't tried it but try separate the line with one space with underscore _

.Pattern = "aaaaaaaaaaaaaaaa _
            bbbbbbbbbbbbbbbb _
            cccccccccccccccccc"

Also you used [a-z]{1} which I'll use again, but how do you write all numbers to be omitted like 1-9???? Would it just be;
[1-9] or [1-9]{1} ???????

[1-9] will not include 0, so if you want to include 0 then [0-9]
ex.
abc company 123 2 f
matches
2 & f
{1} means number of appearance
{1,3} = 1 to 3 appearance, this matches "abc" & "123" as well

If you can find out why it returned a space but with 40 appearances that would be fantastic. I'm not sure what that is?

I guess the data has been imported from another application.
Can you just enter in any cell to test

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

and get back to me with the result?

1 last question Jindon.
You can just answer yes or no, I'm just curious actually.
I've just read how to create a macro button (So I can assign this macro to a button and put it on a sheet. Actually, I want to create a template with the macro and custom button assigned to it, and have the button showing on the sheet in the template.

My question is quite simply;
In your code that you have written Jindon. Can code be written so that it creates a button for a sheet within the formula, or do you always have to write code/ then make a button and assign it after ?
Just wondered that's all.

OK what I would do in your case is to create .xlt(Template) file

With "SiamSites Full List 1" sheet
1) add a commandbutton from the activex toolbox
2) right click on the sheet tab [View Code] then paste the following code

Private Sub CommandButton1_Click()
    test
End Sub

Clear all the data and
3) go to [File] - [SaveAs] select ".xlt" extention from the file typedropdown
4) specify the directry that you want to save it to.

so that, everytime you open the file, it creates New .xls file with everything in it, but the .xlt file always stay the same.

 

[John Caines]

reply

Many thanks for your reply Jindon.
I'm just starting to read it now.

This excel is so powerful.
I told you I'm a beginner.
I've just spent several hrs learning how to do several things as I want to format a sheet like;

Alternate coloured rows. (IE Format /Conditional Formatting)
A piece of software I have saves xls sheets with nice alternating colour of light grey and I've been trying for ages to work out how the software does it. It's a custom colour so I couldn't see it in my palette.
I've just found out that you can go to;
Tools/options/Color...,. and then modify etc.
I got his colour using a colour picker (Pixie) so I have the RGB number. Now it looks good as I have the custom colour I've wanted for ages and alternating coloured rows which I've tried to do for ages also.

Your formula works fine Jindon in a blank sheet with say just 1 column of keywords. It's excellent.

I've just read what you've said about using a space and an underscore to make the row of code not 1 mile long
I used the following but I got an error;
==============================
With CreateObject("VBScript.RegExp")
.Pattern = "\b([a-z0-9:\;&\+-/\|\\]|200|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)|fe|ga|hi|i(d|l|v)|the|for|(g|t)o|in|up|you(r)?(rs)?| _
by|do|not|we|from|get|l(ike|ook)|that|with)\b"
.Global = True
.IgnoreCase = True
============================================
Have I done it right as you said?? I think I understood what you meant, but it didn't work

I'll read through all your post now Jindon. It's 1.30am but I can't get off this excel at the moment.
Your formula at the moment Jindon Looks 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("sheet1")
a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("VBScript.RegExp")
.Pattern = "\b([a-z0-9:\;&\+-/\|\\]|200|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)|fe|ga|hi|i(d|l|v)|the|for|(g|t)o|in|up|you(r)?(rs)?|by|do|not|we|from|get|l(ike|ook)|that|with)\b"
.Global = True
.IgnoreCase = True
.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

===========================================
Again many thanks
John Caines

 

[jindon]

Re: 99.9% It's brilliant.

Try with one line first
if you haven't got error with the code then try to separate as you did.

.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"

 

[John Caines]

more info on C17

Hello Jindon,

I've just done what you've said with the following; You wrote;
=============================================================
I guess the data has been imported from another application.
Can you just enter in any cell to test

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

I typed the above in C17 and hit enter and the following happened;
It came up with a pop up box about saying;

"Microsoft Office excel cannot calculate a formula. Cell references in the formula refer to the formula's result,creating a circular reference. Try one of the following;
If you accidently created the circular reference, click OK.
To display the Circular Reference toolbar, point to Toolbars on the view menu, and then click Circular Reference."

I had to click off this pop up info box Jindon. When I did, the cell now has a "0" in (Without quotation marks)
Not sure what all this means???????

I did look through the list to see if it meant there's 40 occurances of "0" but it's not that.

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

Also you wrote;
=============================================================
{1} means number of appearance
{1,3} = 1 to 3 appearance, this matches "abc" & "123" as well
=============================================================

So, I tried inserting {1,4} Reading what the above means, if I insert this into the ignore line of code it will ignore numbers that are 4 numbers long??? so anything like 2001, 2007, 1999 etc etc or 123, 999, etc etc will be ignored??

I've inserted it like this Jindon
I wrote;
============================================
.Pattern = "\b([a-z0-9{1,4}:\;&\+-/\|\\]|200|a(ll|m|n|nd|ny|t)|c(a|o|om)|o(f|n|r...... etc etc etc
============================================
Strange; with the above; Cell C17 is still empty but under appearances in col E it has a value of 48
And no numbers are returned in the list.

If I use the following; .Pattern = "\b([a-z}:\;&\+-/\|\\]|200|a(ll|m|n|nd|ny|t)|c(a|

Cell C17 is still empty but Col E 17 has a value now of 44???????


Very Strange.

I'll post this now, and read the rest of your post.
Many Thanks again Jindon

 

[jindon]

John

1) do not enter that formula in the cell C17
2) Pattern I posted last is different from yours.

 

[John Caines]

line of code

It's no problem really Jindon being 1 big line.
I've just tried again to put it on 3 lines using a space and underscore,, but it didn't like it. No problem.
I'll carry on reading your post about the template now.
Thanks
John Caines

 

[John Caines]

1 more question

Sorry about this Jindon.
I do have 1 more question.
But I'm sure you'll know the answer.
I'm formatting the workbook (or template ) that has 3-4 sheets.

If say I have on sheet 1 ,,6 columns only I want to display,,,, how do I delete or hide all the columns greater than say column g????

I've tried all sorts.. I can see how to highlight several columns and then just right click and select "Hide" but that just moves all the columns up one?? ie to the left again.
I've looked at the microsoft help,,, and it basically tells me what I've just mentioned.
Am I missing something here???? Or do you have to write a little formula saying something like,,
"hide all cells to the right of column g"

I hope you can tell me the answer Jindon. I just can't see it honest.

Many Thanks
John Caines

 

[jindon]

Re: 1 more question

If say I have on sheet 1 ,,6 columns only I want to display,,,, how do I delete or hide all the columns greater than say column g????

1) Select All the Cells and right click - CellFormat - Unlock
2) Select All the columns from "h" to "IV" and hide them
3) Protect the sheet

Is this what you wanted? or via vba?

Sub test2()
Dim ws As Worksheet
For Each ws In Sheets
    If ws.Name <> "Sheet1" Then   '<- specify the sheet name that you don't wan to run the code
        With ws
            .Unprotect
            .Cells.Locked = False
            .Columns("h:iv").Hidden = True
            .Protect
        End With
    End If
Next
End Sub

PS
have you tried the formula in the other cell than cell C17?

 

[John Caines]

jesus brilliant

Thanks for that about hiding cells Jindon. Brilliant. Really.

As to the C17 problem.

I've used the same large keyword list.
I typed in a differnet search string. Just for the word "web".
It's returned many results.
But look at c14?? 588 appearances,,, but nothing there???

I'll attach the picture now Jindon.

New Microsoft Excel Worksheet.xls
	A	B	C	D	E	F	G
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%
17	accessdesignhostinginternetweb		promotion		379		1.61%
18	accessdesignhostinginternetwebasia		development		316		1.34%
19	accessdesignhostinginternetwebthailand		software		296		1.26%
20	adfreewebhosting		flash		280		1.19%
21	adfreewebhostingasia		company		201		0.85%
22	adfreewebhostingthailand		template		184		0.78%
NicheKWresults

Hope this makes sense??

Many Thanks Jindon
John

 

[John Caines]

Sheet

Here it is with cell c14 Jindon.
I don't think I posted the last 1 correctly

New Microsoft Excel Worksheet.xls
	A	B	C	D
1	PhrasesThatInclude:web		UniqueKeywords
2	TotalPhrases:4453		Total:305
3
4
5	3dflashwebdesign		web
6	3dflashwebdesignasia		design
7	3dflashwebdesignthailand		site
8	aecommercewebpagehost		hosting
9	aecommercewebpagehostasia		asian
10	aecommercewebpagehostthailand		asia
11	awebhostingwebsitedesign		thailand
12	awebhostingwebsitedesignasia		ecommerce
13	awebhostingwebsitedesignthailand		free
14	aaadesignhostingsiteweb
NicheKWresults