Searching for multiple keywords in multiple sources of text

[Bart..]

Hello everyone,

I have an interesting problem that I previously tried to combat with normal formula's but I think having a VBA macro is going to be way faster.

I have text in 4 different columns D:G on sheet "Rawdata input" and a big changing keyword list in sheet "Keywords" that starts at G5. I would like to run a lookup per column for every synonym and then return all synonyms concatinated per column in columns A:D in sheet "Output". Also every synonym has a main keyword above the synonym list in G2. I also would like to return the main keyword in column E on the output sheet. So if column D has 4 synonyms returns I would like to concatinate them in column A on the output sheet and if column E has 2 returns I would like to concatinate those in Column B etc. etc. Once keywords lookup is done-all keywords should be separated by symbol ",".

So as follows:
- Column D:G on sheet "Rawdata input" contains of large text
- Column G5.currentregion on sheet "keywords" contain the always changing synonym list
- Column G2 on sheet "keywords" contain the main keywords
- Column A:D on sheet "output" should return synonyms per concatinated per column with a "," as separator. So synonyms from D on the rawdata list go to column A on the output list, E to B, F to C and G to D.
- Column E on sheet "output" should return the main keywords found

I really hope i made myself clear as it might be really confusing, thats why I made a sample sheet:

thank you for any help you can provide!

Bart

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Bart..]

Thank you! added it in now

[AliGW]

Thread moved to the VBA section. Please do NOT open duplicate threads (see the forum rules). Thank you.

[jindon]

One way

Please Login or Register  to view this content.

[Bart..]

Thank you so much jindon !! it blows my mind that you can do this. I cant even navigate an forum properly and you did this. Thats insane


Thank you so much again!

Original Post:

Thank you so much jindon !! it blows my mind that you can do this.

I had one problem though (which is my mistake for not saying it). Sometimes the cell with text is empty, now it gives an error. Is there an easy way to fix this?

I get a typ mismatch error on this part:

Please Login or Register  to view this content.

Please do not post-edit posts in such a way that subsequent posts make no sense. Thank you.

[AliGW]

@Bart

I've added code tags for you, which are required when you post code, please.

[jindon]

Bart

Thanks for the rep.
I down loaded the file that I uploaded and run, but it is very slow. (When I tested before I upload, it was fast enough...)
This one should be faster...

Please Login or Register  to view this content.

[Bart..]

Hey,

Thanks again! this does indeed run faster, super helpfull ones again :D