How to Creating a Unique Keyword List?

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,155
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
i am sure there is a better way (hope nobody will shoot me for this code :biggrin: )

assumptiuons:
your data is in sheet1
your unique key word list will be transported to sheet2

try this

Code:
Sub key()
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")


lr = ws1.Cells(65536, 1).End(xlUp).Row

For i = 1 To lr

With ws1
    .Cells(i, 1).TextToColumns Destination:=.Cells(i, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    lc = .Cells(i, 256).End(xlToLeft).Column
    .Cells(i, 1).Resize(, lc).Copy Range("d1")
    .Range("d1").CurrentRegion.Copy
    
    .Range("k1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        
    .Range("k1").CurrentRegion.Copy
    fr = .Cells(65536, 15).End(xlUp).Row
    .Cells(fr, 15).PasteSpecial (xlValues)
    .Range("d1").CurrentRegion.Clear
    .Range("k1").CurrentRegion.Clear
    lr2 = ws2.Cells(65536, 1).End(xlUp).Row + 1
    .Range("o1").CurrentRegion.Copy ws2.Cells(lr2, 1)
    .Range("o1").CurrentRegion.Clear
End With
Next i
With ws2
    .Range("a1").Value = "Key"
    .Range("A1:A9").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D1" _
        ), Unique:=True
    .Range("a1").CurrentRegion.Clear
    .Range("d1").CurrentRegion.Copy .Range("a1")
    .Range("d1").CurrentRegion.Clear
End With
End Sub


far from perfect but it worked here

HTH

Roland
 
Upvote 0
Try this:
Goto DATA > Filter > Advanced Filter > choose "copy to another location", Type the range ( A3: A100), copy to "B1" (or wherever you want", click "unique records only" and press OK.
If this does not work, Tell me what is the problem you are facing
Ravi
 
Upvote 0
Here's another possible code solution - stick it in a standard module and with the sheet containing your list being the active one, click Run:

Code:
Sub test()
Application.DisplayAlerts = False
Columns(1).Replace What:=" ", Replacement:=Chr$(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Columns(1).Copy
Workbooks.Add
Range("A1").PasteSpecial
With ActiveWorkbook
    .SaveAs "C:\test.prn", FileFormat:=xlTextPrinter
    .Close
End With
Workbooks.OpenText "C:\test.prn"
End Sub
 
Upvote 0
Roland

We have both provided solutions (and by their nature they are "quick&dirty" - ie they aren't optimized) so either one will do the job. However, depending on the initial list length, my code is very fast because it isn't iterating thru anything using VBA (it's all done using Excel's in-built functionality).

Different strokes for different folks! It's always nice to have a choice of approach!

:biggrin:

Merry Christmas!!

(y)
 
Upvote 0
Update, & 1 more question & Thankyou all:)

First of all I just want to say a real big thank for all your replies.
Really, I feel overwhelmed actually.
I am a real beginner in excel. I have a couple of things I've always wanted to do, so I thought I'd try a post on this forum, and the speed of the response from you all is amazing.
I really am very grateful.

As to the 3 different choices of ideas(Formulas) you've sent,
I could only get 1 to work.
First off, that's not to say that 2 are wrong. I'm a real newbie like I said, so I'm sure I did it wrong somehow.
The way you told me to do it Ravishankar, well, I tried it, but it just copied the first cell across and not all the data of my 2674 odd lines of keyword phrases?
You wrote;
-----------------------------------
Goto DATA > Filter > Advanced Filter > choose "copy to another location", Type the range ( A3: A100), copy to "B1" (or wherever you want", click "unique records only" and press OK.
-----------------------------------------
Well, I went advanced filter and in the "List Range it said $A$1:$A$2674
So in the criteria range I type A3: A100 ??????? (I have tried also A1:A2674 as well and then copy to B1 (unique records only) but it just copies the first row over and nothing else?? and with A1:A2674 it just copies everything (Duplicates the rows ) all into Column B.??
Not sure If I've done what you asked me to do right there:(

As To your suggestion "mrroland" (How the hell did you work out that code?? It's beyond me)
I can't remember what I done now.:) Probably my fault why it didn't work.

And Richard,
Well. Cheers mate.
Worked a dream.Really. Thanks awfully for this.
I will just explain step by step what I done if I may, just so if I have done something wrong, maybe you could point it out??
ok. This is what I done;

1. Opened my workbook. All Phrases are in Column A (All 10,654 of them):)
2. Go to Tools/Macro/Macros/
3.Enter name; test in the Macro Name box then click the create tab
4.Enter the formula in the In the Book1 - Module1 (Code)
-----------------------------------------
Sub test()
Application.DisplayAlerts = False
Columns(1).Replace What:=" ", Replacement:=Chr$(10), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns(1).Copy
Workbooks.Add
Range("A1").PasteSpecial
With ActiveWorkbook
.SaveAs "C:\test.prn", FileFormat:=xlTextPrinter
.Close
End With
Workbooks.OpenText "C:\test.prn"
End Sub
-----------------------------------------------

5. Click Run / Runsub/UserForm

That's it, it does it's thing.
6. I then go to the New Workbook(Is it a workbook?? It isn't a sheet from the original Book but a separate sheet? & it's called Test.prn). (Not sure what prn is?)
Just "Googled It:) I went here;
http://www.frogmorecs.com/arts/what-is-a-prn-file.html
It said;

What is a PRN file?

A PRN file is a special type of file which contains instructions for a printer, it tells the printer what to print on the page and where as well as which paper tray to use, what the paper size is and a number of other controls.

OK.I then;
7. Highlight the whole Column.
8. Click Data/Filter/Advanced Filter. But then A message comes up??
9. I'm not too sure exactly what it means but I click ok anyway.
10. I then have checked "Filter the List, In-place
List Range is $A$1:$A$29810
I check "Unique records only"
I then hit OK

11. That's it I think? Sorted.

Two questions on this though if all this is ok.
By the way, when I opened the list I had to double click on the "A" column to maximise the width, as when I ran it without doing this the results that came back weren't right. Some of the words were cut off at the end? But when the column was maximised it was ok.?? I don't know why that is. Anyway,, The numbers to the left of Column "A" How do I get them to be 1,2,3,4,5 etc etc. So I know how many lines I have?? As now they show 1,2,3,4,9,14,15,49,121,354 etc etc as it's obviously deleted the non unique ones.
I know this is probably easy , But I can't see how to do it?? Looked at the Help, couldn't see it there??
And second question.
Is it possible to write the code in such a way so that it returns the same data but automatically deleting the duplicates??? Just a thought.If it could be done that would be amazing.



OK. A big thanks to evryone for your help.
I do have 1 more question if I may.
I'm really grateful you've managed to solve my problem that has bugged me for ages, but actually, there are 2 things I've always wanted to do.The first you have managed to answer for me, and I truly am grateful. The other being the following if anyone can help me please (And I hope you can understand it:))

I've good a huge list of keyword phrases. 10,654 rows of phrases like "world wide car imports"
In my original post I've asked if there was a way to sort it to return all the unique words to this list, which you've managed to crack for me.:)
But what I want to do next is be able to say this;
Maybe in 1000 rows of the phrases there is the word "Access",, Maybe 1200 rows contain the word "Powerpoint" maybe 1342 rows contain the word "Excel" (I think you get the picture:))
What I want to do is this
Ask (Type in somewhere a question) like, All the ROWS that contain the word "Excel" Export all the words that are also ONLY in those rows to another sheet.
So, effectively I'm creating a list of words that are only associated with the word "Excel" as the list that would be generated would be all the words in the rows ONLY with the word "Excel" in it and export them

So, I'm kind of looking for related words to words I choose within the rows.Niches I suppose.

I hope this makes sense.
Is there a way of doing this in Excel???
This is all beyond me.
If anyone could manage to do this for me I would be most grateful. I'm just not up to the kind of formula building that you have provided me with in your answers:(

Again.
Many Thanks for all your help

John
 
Upvote 0
John

Where exactly would Excel, Access or Powerpoint be located in the phrase?
 
Upvote 0
location of Phrase

Hello Norie

Could be anywhere:)

I'll give you a couple of examples to try and clarify what I'm trying to achieve;

Imagine I have 10,000 phrases like;

microsoft excel help
windows powerpoint for display
word documents
best excel spreadsheet software
add ons for excel
microsoft word videos
seminars for advanced excel
all day seminars on window products like powerpoint

Hope you get the idea?
this would be a list 10,000 long.
1 phrase per line.
could be 1 word on a line or 10-15 even.

So what I want to say is
Please return results, for the main root word (Which I choose) of say "Excel" (I would type this in somewhere.)
Then a macro or whatever would run and create a list of all the other words that were in the phrases that only contained the word "Excel" (In other words on the same line as the word Excel)
And if it could the results it returned would be unique also, (Not returning duplicates) if this was possible. If not I could do a sort after the results had been returned, and then remove the duplicates. Better if it could do this also though.

I hope this makes sense Norie.
I'm really a beginner in excel and I have not got a clue how to do this, and write the formulas etc.
Hope this is possible.
I hope it is.
Merry Christmas by the way
John
 
Upvote 0
John

Assuming that all these phrases are in column A (I think this was Norie's point - are they all over the shop, or in one column?) then you could use autofilter to do this. Data>Autofilter, and select Custom from the dropdown arrow and choose "contains" from the list and type in eg "Excel" (without the quotes) - this will return all rows containing the word Excel. You could then copy and paste these in to another sheet if required.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top