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]

sorting

Hay, Thanks Richard,
Sorry Norie,

It seems what you were asking me went right over my head

Yes they are all in Column A.

I've just tried what you suggested Richard. I never knew this function even existed actually
It is very good.Yes, This does sort by a word that I type in. I can then copy and paste like you said to another sheet, and then run the function you posted before to get all the unique words from it.
(I saved it Richard, the formula you posted earlier to sort by line unique words as a workbook and called it Unique Keyword Finding Spreadsheet)
Is there not a way to save it as an add on or something so whenever I start excel from the main Microsoft Excel Icon (the formula you gave me Richard )is installed into excel on startup of any sheet??(Rather than me starting up my now saved workbook that is called "Unique Keyword Finding Spreadsheet" all the time??

Also, the above works great, but is it possible that it could auto sort the sheet and save all the unique keywords (In 1 column, 1 per cell, on say sheet 2) in the rows relating to my chosen word??
Just a thought.
Whilst I'm here, something has bugged me for ages also. Probably simple, but when I do sort a list. Say from 1000 to 670 lines.
Is there anyway of saving the sheet so it only shows the 670 lines??
A few times I've created a lot smaller list from a big one then I've changed the background to a nice yellow say, then added a few headings and a border around the info.
But how do I delete all the cells below and to the left of my data???
I've tried looking in Help, but I must be searching for the wrong thing as to how to do this.
All I want to do is save my now nice looking sheet of data and not have 1000's of cells to the bottom and left of the data.

Hope you can help

Many Thanks again
John

 

[Norie]

John

What are you actually trying to do here?

 

[John Caines]

What I'm trying to do

Hello Norie.

You've asked, "What am I actually trying to do here?"

OK. Here's what I'm trying to do Norie,

I have some keyword software.
I've made several lists of keywords, usually no more than 1000 phrases, but I've just made a really big one of over 10,000 phrases.
OK, so, I open an excel workbook.
All the way down column A I have my list.(I'm only dealing with 1 list of keywords)
So that's 10,000 lines, each with a phrase on it.

To elaborate slightly on the phrases;
Say I use my keyword software and the main Root word is "Car"
It digs deep searching google etc and comes back with 10,000 phrases.
Now, with a list this big there are going to be for example 20-30 niches related to the main root word of "Car"
(I hope this is making sense so far
These niches could be for example "Car Insurance", "Car Hire" "Car's For Sale" ETC ETC
So the niches are

insurance
hire
sale
etc etc

within each niche there would be maybe 100-400 (just as an example) phrases related to each niche,
So, take the niche "Car Insurance"
There might be several 100 phrases related to this niche,
ie
car insurance new york
cars for insurance
ladies car insurance
car insurance online
cheap car insurance los angeles,
etc etc.

So, what I'm trying to do, if possible is the following.
I want to be able to say either of 2 things.
1. I want to be able to type in a word, like "Insurance " somewhere into excel, then, excel will look at my 10,000 word list and return all the rows with the word "insurance" in to another spreadsheet, AND if possible, somewhere on the same spreadsheet, return a list of all the words (Having removed the duplicates) that are contained within these rows.
That's really what I want to do Norie.
As this would be a niche of phrases related to the word "Insurance" and all the semantic, words that are related to this word (Insurance) also in a list

Richard has been great, and posted 2 answers. Both really helpful, but neither 100% exactly what I'm trying to do (As state above)
He posted a formula (Macro I think you call it) that looked at my list of say 10,000 phrases (All in column A, 1 phrase each line). When I hit the run button it returned all the words individually, on separate lines.
This was great, but, It didn't remove all the duplicates. and just sorts the main list without the functionality to find/ or sort niches on auto mode

Also, another suggestion was of ; having a list and using the "Data/Filter/Custom Filter" then typing in a word and it would then sort the list showing only lines with that word in, for example,, if I typed in the word"Insurance" it would only reveal lines of phrases with the word insurance.
Again, this was great (As I didn't even know this function existed but you then have to copy the rows, paste them to another sheet.
Then run the macro to get all the words separate on 1 line. then remove the duplicates etc
I do want to keep the insurance list as a niche of the main list, but what I also wanted was to then be able to find all the unique words on that list with the duplicates removed.
Richards Macro formula puts all the words on separate lines in another sheet but it didn't take out duplicates automatically. And you had to do the custom filter by hand first.

I really hope this all makes sense Norie.
The answers I've had so far work, in a round about way, but aren't 100% to what I'm really trying to achieve all within a couple of clicks (Hopefully)

So, t recap.
1. I have 10,000 keyword phrase list, all in Col A ,, 1 phrase per line.
I want to either,
1. just click, or run a macro or something that will automatically find the niches for me (Probably it will do this by me imputing somewhere a number like 20, so it finds the 20 most used words excluding conjunction words or words of 1 or 2 characters) then it sorts out 20 pages(sheets), each page has a list in col A of all the phrases related to that certain niche, AND then in say col E on the same sheet (I said col E to give it a gap visually), it will list all the unique words to those phrases (Having deleted duplicates also)

If the above isn't possible, as I'm no programmer, but I might be asking too much here, alternative 2 is.
I just type in somewhere a word of my choice, ie "Insurance" then the macro finds all the rows with the word "Insurance" in it and puts this on another sheet, and if possible, also on the same sheet but in a different column obviously, say col E (I like that col E it creates a list of all the words within this filtered list of phrases for insurance, and it automatically deletes duplicates also.

I hope all his makes sense Noire.
Not asking for too much am I
By the way, just looked at the time.
00.08am
So, Merry Christmas to you all
I'm sat at my PC typing on an Excel Forum on Cristmas Day! Like it

Hope you can possibly help Noire.
Maybe I'm asking for something that can't be done, I'm not too sure.
But hope you can help
Many Thanks
John
PS.
In my last post there was something that I know must be simple to do, but I just don't know how to do it,

I'll copy and paste it again now. I did look min the help, but I couldn't find the answer,
here it is anyway.
I wrote

"Whilst I'm here, something has bugged me for ages also. Probably simple, but when I do sort a list. Say from 1000 to 670 lines.
Is there anyway of saving the sheet so it only shows the 670 lines??
A few times I've created a lot smaller list from a big one then I've changed the background to a nice yellow say, then added a few headings and a border around the info.
But how do I delete all the cells below and to the left of my data???So the sheet only shows the data and no cells around it?
I've tried looking in Help, but I must be searching for the wrong thing as to how to do this.
All I want to do is save my now nice looking sheet of data and not have 1000's of cells to the bottom and left of the data.
-------------------------------------------------

Merry Christmas again all of you
John

 

[Krishnakumar]

Hi,

I just type in somewhere a word of my choice, ie "Insurance" then the macro finds all the rows with the word "Insurance" in it and puts this on another sheet, and if possible, also on the same sheet but in a different column obviously, say col E (I like that col E it creates a list of all the words within this filtered list of phrases for insurance, and it automatically deletes duplicates also.

Try,

Sub TestIt()
Dim ws  As Worksheet, i As Byte
Dim a, v, x, FindStr    As String

On Error Resume Next
Set ws = Sheets("FinalResults")
On Error GoTo 0

If ws Is Nothing Then
    Set ws = Sheets.Add
    ws.Name = "FinalResults"
    GoTo DoThis
Else
DoThis:
    FindStr = Application.InputBox("Find What", "Search String")
    If FindStr = "" Then Exit Sub
    With CreateObject("Scripting.dictionary")
        For i = 1 To Sheets.Count
            If Sheets(i).Name <> ws.Name Then
                Sheets(i).Activate
                With Range([a1], [a65536].End(xlUp))
                a = .Value
                End With
                For Each v In a
                    If Not IsEmpty(v) And InStr(1, v, FindStr) > 0 And _
                        Not .exists(v) Then
                        .Add v, Nothing
                    End If
                Next
            End If
        Next
        x = .keys
    End With
    ws.Activate
    With Range("A1")
        .CurrentRegion.ClearContents
        .Resize(UBound(x) + 1, 1) = Application.Transpose(x)
    End With
End If
End Sub

HTH

 

[John Caines]

Formula didn't work

Thanks Krishnakumar for your help.

I've just tried to run this, but unfortunately it didn't work.
I have an error message and the line " For Each v In a" is highlighted in yellow on the code you provided Krishnakumar.

I'll explain;

I have a workbook open with 6 sheets on it.
The main sheet has 10,000 rows of phrases all in column A.
I did the following;

I have the sheet open with the 10000 row list of phrases. I highlighted the row (I'm not sure if you have to do this or not and the formula provided , I'm not sure if it just searches through the open sheet I'm looking at??)

Anyway, this is what I done, hopefully ok,

1.In the Tools menu in Microsoft Excel, I pointed to Macro, and then clicked Visual Basic Editor.
2. On the Insert menu, I clicked Module.
3. I copied your code into the code window of the module.
4. I clicked "Close and Return to Microsoft Excel" from the File menu.
5. I was then back at my open sheet with the 10000 row of phrase all in Col A and I have highlighted the ColA.
6. I went to Tools/Macros/Run
7. A small window appeared called search string saying "find what"
8. I entered a keyword ie "hosting" and clicked ok.
9. An error came up called Microsoft Visual Basic saying, "Run-time error '13' Type mismatch
10. I clicked debug
11. It took me to Visual Basic and the copy of your formula with a line of code highlighted in yellow, which was" For Each v In a"

Hope this makes sense Krishnakumar.
I wish it would have worked.
I think it must be that line of code.
This is all beyond me.

Hope you might be able to look at it for me if you can.
It all looks promising though
Thanks for your reply Krishnakumar.

Have a great Christmas
All the best
John

 

[Krishnakumar]

Hi John,

Happy X'mas to you too!

Try,
replace the old code with the following

Sub TestIt_v01()
Dim ws  As Worksheet, i As Byte
Dim a, v, x, FindStr    As String

On Error Resume Next
Set ws = Sheets("FinalResults")
On Error GoTo 0

If ws Is Nothing Then
    Set ws = Sheets.Add
    ws.Name = "FinalResults"
    GoTo DoThis
Else
DoThis:
    FindStr = Application.InputBox("Find What", "Search String")
    If FindStr = "" Then Exit Sub
    With CreateObject("Scripting.dictionary")
        For i = 1 To Sheets.Count
            If Sheets(i).Name <> ws.Name Then
                Sheets(i).Activate
                With Range([a1], [a65536].End(xlUp))
                a = .Value
                End With
                On Error GoTo Nxt
                For Each v In a
                    If Not IsEmpty(v) And InStr(1, v, FindStr) > 0 And _
                        Not .exists(v) Then
                        .Add v, Nothing
                    End If
                Next
            End If
Nxt:
        Next
        x = .keys
    End With
    ws.Activate
    With Range("A1")
        .CurrentRegion.ClearContents
        .Resize(UBound(x) + 1, 1) = Application.Transpose(x)
    End With
End If
End Sub

HTH

 

[John Caines]

won't work again

Hello Krishnakumar,

I've tried the new code.
Same problem.
Wouldn't run

Same error in the line of code
This is highlighted in yellow;
----------------------
For Each v In a
--------------------------

If you PM me I can email you the spreadsheet.
But I expect you might be able to try it with a sheet of any phrases all in ColA on sheet 1.

Your formula does make a new sheet called "Final results"
But it just comes up with the error??

Hope this makes sense.

Many thanks for trying though. On Christmas day as well

All the best
John

 

[jindon]

Hi
try

Sub test()
Dim ws As Worksheet, a, e, dic As Object, b(), m As Object
Dim n As Long, myTxt As String, txt As String
Set dic = CreateObject("Scripting.dictionary")
dic.CompareMode = vbTextCompare
On Error Resume Next
Sheets("Result").Delete
Sheets.Add.Name = "Reuslt"
On Error GoTo 0
myTxt = Applilcation.InputBox("Enter a word",Type:=2)
ReDim b(1 To Rows.Count, 1 To 2)
With CreateObject("VBScript.RegExp")
    For Each ws In Sheets
        If ws.Name <> "Result" Then
            a = ws.Range("a1",ws.Range("a" & Rows.Count).End(xlUp)).Value
            If IsArray(a) Then
            For Each e In a
                If InStr(1,e,myTxt,1) > 0 Then
                     txt = Replace(e, MyTxt,"")
                    .Pattern = "[,\?\*\.\|\{\}\\\[\]\(\)!]"
                    .Global = True
                    txt = .replace(txt,"")
                    .Pattern = "\S+"
                    .Global = True
                    For Each m In .execute(txt)
                        If Not dic.exists(m.Value) Then
                            n = n + 1
                            b(n,1) = m.Value : b(n,2) = 1
                            dic.add m.Value, n
                        Else
                            b(dic(m.Value),2) = b(dic(m.Value),2) + 1
                        End If
                    Next
                End If
            Next
            Else
                If InStr(1,ws.Range("a1").Value, mtTxt,1) > 0 Then
                    txt = Replace(ws.Range("a1").Value, myTxt,"")
                    .Pattern = "[,\?\.\*\|\{\}\[\]\(\)!]"
                    .Global = True
                    txt = .replace(txt, "")
                    .Pattern = "\S+"
                    .Global = True
                    For Each m In .execute(txt)
                        If Not dic.exists(m.Value) Then
                            n = n + 1
                            b(n,1) = m.Value : b(n,2) = 1
                            dic.add m.Value, n
                        Else
                            b(dic(m.Value), 2) = b(dic(m.Value),2) + 1
                        End If
                    Next
                End If
            End If
        End If
    Next
End With
Set dic = Nothing : Erase a
With Sheets("Result").Range("a1")
    .Resize(,2).Value = [{"Word","# of appearance"}]
    .Offset(1).Resize(n,2).Value = b
End With
End Sub

 

[John Caines]

Big Apology To Krishnakumar.

I'm really really sorry about this.
Krishnakumar.
I owe you a big apology here.
I told you your code didn't work.
I'm not sure what the hell I was doing with it yesterday, but I've tried it today and it works fine!!!!
I feel really really bad about this.
You are all busy people I expect, and here I am, completely green as grass, asking you to help me.
You're all good enough to help, then I tell you your Formula doesn't work, and it does!!!!!!!

Again, I am really sorry.
To explain a bit further though,
Also Jindon,
I've tried your formula this morning at 6am as I was checking my emails, and yours didn't work for me, and I've just typed quite a large text file explaining the error, but I tried it another way and it worked fine!

This is the trouble I think.
The big problem with everyone's life now isn't money (Well, it partly is)
It's TIME!
For me, a newbie, I know what I want to be able to do, but really, I need to start from page 1 of the excel learning book and not dive into page 300! It's all about time.
I've obviously tried to "wing it" and thought I knew what I was doing, but It's been kind of trial and error, because of time.

So, A big apology again to Krishnakumar, for thinking his formula wasn't any good, when it worked perfectly in fact.

OK. 2 or 3 points though, as Jindon, yours did work 1 way, but did the other of the 2 ways I tried, but Krishnakumar's formula worked both ways.
I will explain both ways and Jindons error message.
1 question first though.
Krishnakumar and Jindon, both your code achieves the same thing.
But Jindons code is much longer than Krishnakumars.
My question is;
If both of them worked perfectly, (Slight problem with Jindons at present which I'll explain in a mo) is it always better (As a rule of thumb) to go with the code that's the shortest????
Again, I'm not a programmer, a real novice, but Krish, your code is only 42 lines long where as Jindons is 87 lines long. (More than double.
Is it best to use the shorter than the longer code or the other way around???
By the way, both these achieve the following;
I'm able to type a word/or words into a box and it looks at all my 10000 long list and creates a separate page with all the lines that contain the word that I'd entered into the box (So anyone reading this knows what I wanted to be able to do)

OK. I'll quickly explain Jindon the error code when I ran your formula a certain way. It worked 1 way, but not the other, but Kris, yours worked both ways.

Here's briefly the 2 ways I tried to run this.
Example 1.
1.go tools/macro/visual basic
1a. then go "Insert" / module/ and paste code into module1
1b. click run/ then click run sub/user form
1c. a box appears saying "Macros" with it called "test" so I click "run"
1d it then says "Compile error, Duplicate declaration in current scope (Whatever that means) And the cursor is pointintg to line 3 here;
Dim n As Long, myTxt As String, txt As String, b()-------(The cursor is between the "String," and the "b()"
1e. I click ok and the "sub test() on line 1 of the code is now in yellow

-----------------------------------------------------------------
That was the way it didn't work on Jindons code, I've just tried this method exactly the same way with Kris's and it worked (Sorry Jindon)

Again, I'm so sorry to Krish, I really don't know what the hell I done wrong with it yesterday.
OK. the way both worked.
-----------------------------------------
way 2, (Working for both;
1a. go to tools/macro/vb
1b. go "insert"/ module,
1c. copy code into box.
1d. go "File" / close and return to excel
1e. go tools/macro/ macros


Sorry, as I'm typing this I'm trying all different was, and I can't get it working now????
I'm sure it worked 1 way Jindon. I just can't get it working. Sorry Jindon.
Keeps coming up with the error Like I mentioned above.
Feel bloody supid again. Saying yours works 1 way Jindon, now I can't get it working anyway
--------------------------
OK. To wrap up.
A big sorry to Krish again for saying his formulas didn't work when it did.
Sorry to Jindon. Thanks for you time on this Jindon, I feel guilty as you spent time trying to help me when Krish's formula that actually worked fine. I just don't know what I done wrong yesterday , Honestly.

In my previous post I did ask if also it was possible to do 1 more thing also.
Seeing as Krish's formula works fine (It's me that doesn't), I'm half way to what I'm really trying to achieve.
Again a big thank you to all concerned.

The other thing I wanted to do was;
Krish's formula looks at my 10,000 rows of data, then a box comes up, I imput the word (Or words) I want it to find. It then (On another sheet) creates a list of all the rows containing the word(Or words) that I entered into the pop up box.
That's amazing. Great. But I wanted to be able to do 1 more thing.
Can, on another sheet (or in the same sheet but a few columns apart from colA,,, ie col F,, can it then give me a list of all the unique words that are in this list it returns??
So, Krish's Formula created say a 1000 row of phrases from the 10000 originally. Lets say it was to find "car hire"
So, Krish's formula gives me 1000 rows of phrase's all with the words "car hire" in them.
So, now I want a list of all the unique words (ie duplicates removed) that are in all these rows.

I hope this makes sense.
This is so, that I have a list of all the words commonly associated with the words "car hire" , kind of symantics.

I hope this could be done.
If possible. maybe even 2 formulas
1 for this function only,(Just symantic unique keyword list with duplicates removed)
and/or a formula (Krish's) that does what he has already been able to achieve, and at the same time also creates a list of unique keywords also (either on the same sheet or another).
So it returns 2 lists at the same time. 1 list with all the phrases containing the entered keyword/or words, and at the same time another list with all the unique keywords from that returned list.


Now That would be amazing!!!

I'll wrap this up now as it's getting kind of long.

Again,
Finally.

Big sorry to Krish and Jindon.


Hope you can help on this final issue.
You've all been great. I really appreciate all your efforts.

Many thanks.
Merry Christmas to you all
John

 

[jindon]

John,

Kris' and mine shouldn't create same results.

My understanding is:

1) Find all rows that include the "Word"
2) pick all unique word out of those rows except the "Word" and punctuations.
3) List unique words and apperance in the result sheet in descending order

If you get an error, tell me which line?