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]

a note

Just a quick note Jindon,

When you've written the above code you have the
.Pattern = on 1 line,,, then all that follows on the next.

But when I copy and paste it into VB it only takes it all on 1 line. I've tried to put it on 2 like you show, but it doesn't let me??
Just thought I'd mention this anyway.

Thanks
John Caines

It's 6am now. I can't get enough of this excel at the moment

 

[jindon]

Ok John, it should be in one line anyway.

I don't know why it is separated in 2 lines...

 

[John Caines]

do you know why Jindon??

Do you know why 1 cell is blank Jindon??

I kept your shorter code Jindon and ran both, screen captured part of both results to compare.

The word design in the Large code (The 1 im using now with the ignore words ) has the word design and it says it occurs 2523 times in colE.

But in the small code (Without the pattern ignore line) it occurs 2519 times????


Hope this helps

John

 

[John Caines]

lg and sm code screen shots

Lg code with the pattern ignore list in.
Look at the word design Jindon in C6 and the occurances
===================================

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%
NicheKWresults

================================
now the short code, look out for the same word Jindon. Different amount of occurances??
====================================

New Microsoft Excel Worksheet.xls
	A	B	C	D	E	F	G
1	PhrasesThatInclude:web		UniqueKeyWords		NoOfAppearances		%OfAppearances
2	TotalPhrases:4453		Total:321		Total:23582
3
4
5	3dflashwebdesign		web		4538		0.192434908
6	3dflashwebdesignasia		design		2519		0.10681876
7	3dflashwebdesignthailand		site		1966		0.083368671
8	aecommercewebpagehost		hosting		1380		0.05851921
9	aecommercewebpagehostasia		asian		1115		0.047281825
10	aecommercewebpagehostthailand		asia		1113		0.047197015
11	awebhostingwebsitedesign		thailand		1113		0.047197015
12	awebhostingwebsitedesignasia		ecommerce		1035		0.043889407
NicheKWresults

 

[jindon]

John

I think it is a control character from the text file which is invisible.

We need to get the Code number for that...

I thought the formula should get that number.

 

[John Caines]

over my head

Sorry to sound stupid Jindon,
But what you just typed,,, I really don't understand at all??

Sorry I'm not much help,, but this is all above me

 

[John Caines]

text file

do uyou mean the text file the phrases are stored in Jindon.??
I'm using TextPad.

under tools/ preferences / there is quite a few preferences.
Under Editor do I have selected
"Use POSIX regular Expression Syntax"??????
Or leave this unchecked???
Will this matter??
Is this something that might affect it?? You mean in my text File???


Or is it in that large line of words to ignore in the VB code??

Sorry I'm not much help

John

 

[jindon]

Re: reply

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!

Can you just enter =Code(C17) in cell D20 again please?

 

[jindon]

Re: code

John
can you just quickly run this code?

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]

c17

I've just done what you said Jindon.
A blue box appears around the word "promotion" in cell C17.

I've then hit enter and the number 112 appears in cell D20

????????????????????????????????????//

Any ideas??