Extract unique strings from excel cells across rows and columns

[quedan]

I need to extract unique strings from excel cells.
I have a spreadsheet with 2 columns each cell in the column contains a long text string.
That long text string is composed of several strings i need to extract. each individual string is of variable lenght but there is always a pattern for example:
apple: pineapple: tomatoe: carrot:
apple: carrot: potatoe:
pear: apple: lettuce: pepper: fish: brocolli:
Each cell's string can contain one or 100 repetitions of the pattern.
each row (and there can be up to 600000 rows) will contain various strings but there will be some repetition.

My goal is to parse the cells and come up with a unique list of strings:
apple, carrot, tomatoe, pineapple, potatoe, pear, lettuce, pepper, fish, brocolli.

I'm thinking i'll need some sort of macro to accomplish this but i have no idea where to startWhat i'm doing manually right now is copy the first unique string i see to a separate workbook, then run a replace all with blank to get rid of all other instances of the string until eventually I have a list of uniques in a separate spreadsheet and no strings in my source

[alansidman]

This might be easier to solve if you post your spreadsheet with only a sample of data. For that sample, also give us the expected results

[quedan]

added sample file to top post.

[quedan]

is this possible?

[FDibbins]

I dont see any file?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[quedan]

i had uploaded the file but not linked it to the post its there now.

[AB33]

Please Login or Register  to view this content.

[quedan]

This works like a charm.
Not knowing anything about VB script can this be modified to work off the currently active sheet rather then the one named one?
also can it be modified to process both column A and column B? its ok if it does one after the other. and then dumps the results in columns A and B respectively on the Results sheet.
I was thinking of something like this but i'm getting subscript out of range errors on line 10

Please Login or Register  to view this content.

[AB33]

quedan,
Please do not reply with quote, it has not purpose, but clutters the space. Just press reply. It is difficult to know which is line10. My code works on the attached sheet. You had "Source" and Results sheets. Could you please attach your worksheet so as to see where the error is and you also said, I missed column B which I did not see. So, when you attach the error you are getting, could also inlcude what is missing and the desired result. I will have a look tomorrow-(dose off for now )and will get back to you at some point tomorrow.

[quedan]

I didnt reply with quote added some of my badly written code which you can feel free to ignore
I figured out the issue with the error it was a typo in my worksheet name.

See attached an updated sample, it has unique strings in both columns A and B. along with two results i'm hoping to get one ideal and one sufficient.
I tried creating a macro that would pull data only from column B (in the excel file) and list it on the results sheet in column B but it does not appear to do anything.

[FDibbins]

@ AB33 quedan did not "reply with quote". He posted a segment of his own code, and was asking questions about it

[jindon]

Do you mean like this?

Please Login or Register  to view this content.

[AB33]

quedan,Fdibbins

Sorry for Quote,I was misread your reply. I just do not like Quote

I have now ameded my code # 7 above and seem to get your ideal solution. I have two codes, one is attached with the macro, but you may not want to clear the signs from the source book, so the code you see on this page does not clear the signs from the source book. Try the this code with your sample.

[HaHoBe]

Hi, AB33,

you might like to take a look at the Forum Rules, especially Rule #6a Feedback:

Never edit a thread or post to which others have already responded

Ciao,
Holger

[AB33]

Holger,

I have lost you!

I have amended my own code. I see people do this, even Arlu, one of the moderators, does it quite often.

[quedan]

Thanks guys.
Jindon's solution is the easiest for me to use.
AB33 the changed solution is having issues when dealing with two columns it splits the text in each cell but then dumps it for each individual cell instead of just selecting the uniques. No matter though it works great with a single column in cases where Jindon's solution has issues due to the number of lines to be processed.

[AB33]

Quedan,
Yes , Jindon's code is short and simple.
I thought your ideal solution is to merge column A and B and then list only unique values from both columns and this is exactly what my code does.