Find and extract specific text data & accompanying numerical data

I have a large matrix that contains many columns, each column contains text data (e.g. specific words) and next to words are numbers representing their frequency counts. Each column contains different words, some words would repeat across columns but, because of different frequency count, they will not appear in one row.

I need to find specific words (e.g. 'AND') and extract them into new cells so that they appear in one row-range.

The input and needed output are in the attached file.

Many thanks for solutions!

Yes, I put all the instances of the word 'AND' manually into the row-range as an example of how extracted text and numeric data should look. BUt I need a forumula or functiona that would select not only this word but also other words that repeat across the matrix which is large (the mock one is just an example). I do need to find and extract the words 'YOU', 'AGAIN' etc as well as sort out the output in a similar fashion as shown in the output example.

So your solution does not seem to be fitting what I want to achieve...

A code is attached

Hi AB33, the attached excel does not seem to contain any code....

There is. Do you know how to access the module- where the code resides. ALT+F11?
I have now attached a button to the code, so that you can press it to run it.

I see, many thanks.
Yet how can I run multiple searches for other words?

Change this line AND

Please Login or Register  to view this content.

to new search word.

Sorry, do not get it

I have came up with a different search code.
When you run the code(is not attached to the button), it will ask you to enter the name of the word you want to search.
You need to go to the code itself and press F5, or go to the menu and choose run.
I will be offset until this afternoon.

Or right click on the button, choose assign a macro- then choose this workbook- then X(The name of the new macro), so the button is attached to the new code.

Hi, the hyperlinked workbook you indicate leads me to some sort of ad...???

What hyperlink! It is the same book as the above, just added another code.

Hi,
Ok, the strange thing is that when I opened your reply one one computer the word 'workbook' in your reply was hyperlinked and once pressed would produce a pop-up window with some commercial in it. Later I opened your reply on another computer and no hyperlink was there anymore. I have no idea why this happened.

On the other account, I copied your code from X macro to try it on my real data matrix and it renders nothing. What needs to be adjusted in the code to make it work?

And one more thing, every time I search for a new word within the mock data, the results are shown in one range. It would be better to have result per each new word to be presented in a new range for better inspection. And it would be good to get the word plus 0 value if the word does not appear in the column.

I hope I'm not asking for too complicate things...
Many thanx!

You need to change the name of your sheet.
The code works on

Set ws = Sheets("Sheet1")

You need to change sheet1 in to the name of the actual sheet name.

Hi,

I did so as you said, changed the name into the approrpiate, yet no result.... What else I might be doing wrong?

You need to go to the code itself and do the following:
While your cursor is inside the code, any where within the code, press F8. This will take you step by step, stepping over each line. Hopefully, we should be able to identify the culprit line.
As soon as you go to the 2-3 third line, the code should ask you for a word search.