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
)
