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

 

[John Caines]

screenshot

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	112	280		1.19%
21	adfreewebhostingasia		company		201		0.85%
22	adfreewebhostingthailand		template		184		0.78%
23	addsitesubmissionurlweb		custom		177		0.75%
24	addsitesubmissionurlwebasia		services		168		0.71%
25	addsitesubmissionurlwebthailand		affordable		148		0.63%
26	addyourwebsiteecommerce		business		148		0.63%
NicheKWresults

 

[jindon]

C17 is the cell that is empty?

If it is not, enter cell correct refernce that has empty value.

PS it should be C14

 

[John Caines]

Yes Jindon,

It was originally C17. But maybe with changing formulas etc it's now C14.

When I searched for the word "internet" C17 was the empty cell. But now it's C14


OK. So, I've gone back and searched for the word "Internet" so the empty cell returned is C14

I've then typed in D20 what you told me.=CODE(C14)
It returned #VALUE!

===========================
You've also just sent me a VB script (I think that's what you call it) Not sure,, anyway,
I've deleted the retrned sheet (So I only have the "AllKW" sheet of all the keyword phrases.
I've then gone to Macro, and run your macro,, but all the keyword phrases have now been replaced with
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
clean(trim(a1))
=================================
??????????????????????????

Is this right??
Not sure what it's done Jindon.

John

 

[jindon]

Re: code

Sorryyyy
this one

Sub CleanData()
With Sheets("AllKW")
    With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Offset(,1)
         .Formula = "=clean(trim(a1))"
         .Value = .Value
    End With
    .Columns("a").Delete
End With
Call test
End Sub

 

[John Caines]

it then deleted them all again Jindon, but replaced all the keyword phrases in ColA in AllKW sheet with "0" (Without Brackets)

 

[jindon]

Yup

Sorry again the code has been edited so can you copy it and try again?

 

[John Caines]

just a quick note,
I tried your other formula;
==================================
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
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(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
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 Key Words", "", "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)]
.Offset(, 4).Resize(, 1).FormulaR1C1 = "=rc[-2]/" & myTotal
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
=================================
This didnt return a blank space??
Sure it's not the line of code Jindon, the pattern 1??
I'll retry the clean now

 

[John Caines]

Is this correct Jindon???
I've just tried it and the .Formula = "=clean(trim(a[\]1)" is highlighted in Yellow??



Sub CleanData()
With Sheets("AllKW")
With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Offset(,1)
.Formula = "=clean(trim(a[\]1)"
.Value = .Value
End With
.Columns("a").Delete
End With
Call test
End Sub

 

[jindon]

OK good news!

Now I have to look at the pattern, but Sorry I've got no time left today.

I will talk to you on Tue here.

Meanwhile please search the web about meta character,

key words are

Regular Expressions vba meta character

Rgds,
jindon

 

[John Caines]

good news-AND

found the word Jindon))))

It's "and"

in your small code, It returned no errors so I screen captured the results, then compared them to the long code with the "pattern" line of code in. This had 24 results but with the empty cell, C14.
Your shorter code had the 24 results with cell C14 being the word "and"

What a detective
007! Sherlock Holmes

I'll try and lok at the pattern and see if I can change it.
Cheers Jindon