Macro to search entire worksheet using keyword from another sheet

[tonisquall]

Hi all,

I really new to macros and I'd like to ask for assistance in using macro in Excel. I'l like to be able to search all the columns of a worksheet using keywords from a column from another sheet. If it gets a hit from the list it would move the whole row to a new worksheet so that duplicates would be minimized.

Say I paste raw data from worksheet A and the list of key words from column A of worksheet B, it would first look for the keyword in entries of column A from worksheet A then go to the next column up to what ever column has an entry of worksheet A and if it gets a match, move the whole row to worksheet C and the ones left would be moved in worksheet D. It has to be done that way as the excel table is not normalized. Is that possible?

Thanks for your replies in advance guys!!!

 

[Rhodie72]

Hi all,

I really new to macros and I'd like to ask for assistance in using macro in Excel. I'd like to be able to search all the columns of a worksheet using keywords from a column from another sheet. If it gets a hit from the list it would move the whole row to a new worksheet so that duplicates would be minimized.

Say I paste raw data from worksheet A and the list of key words from column A of worksheet B, it would first look for the keyword in entries of column A from worksheet A then go to the next column up to what ever column has an entry of worksheet A and if it gets a match, move the whole row to worksheet C and the ones left would be moved in worksheet D. It has to be done that way as the excel table is not normalized. Is that possible?

Thanks for your replies in advance guys!!!

Say I paste raw data from Sheet1 and the list of key words from Sheet1.column("A:A") of Sheets("B"), it would first search for the keyword in entries of from sheets("A").column("A:A") then go to the next column to what ever column has an entry of sheets("A") and if it gets a match, move the whole row

From where?

to Sheets("C") and the ones

What ones? The number ones on every sheets, the sheets with one in them, the remaining worksheets, the untouched cells... need help here.

left would be moved

where?

in Sheets("D"). It has to be done that way as the excel table is not normalized. Is that possible?

hope the blanks can be filled in where there is no information. Imaging you're explaining this to a blind person, because we cannot see what you're looking at. It might help you get the answer you aim for. The answer is potentially yes.

 

[tonisquall]

Sorry as I am really new to using macros. I have a couple of workbooks that wherein the column count are not the same. Some may have a just 10 columns that are used while others may have as much as 92 columns that are used and not all the cells of the columns are populated.
I'd like to be able to search each cell of all the columns ( or row if it's more efficient ) of a worksheet using keywords from a column from another sheet. If it gets a hit from the list the macro would move the whole row to a new worksheet. If it doesn't it moves it to another worksheet.

I've included a screenshot to hopefully better explain what I'm trying to do. Click the link here.
Based on the screenshot say I paste raw data to Sample worksheet and the list of key words to the Keyword worksheet, it would look for a match from cell A1 of Sample worksheet up the end of the entry of the worksheet ( in this example up to cell AK801). If there is a match in a cell the macro would move the whole row to a new worksheet ( say positive worksheet ) and if it doesn't it would be moved to another worksheet ( scanned entries ).
The raw data has a header so maybe it could be used as basis on how many columns has entries ( if it matters ).