Check a cell for a list of data, if found then print row

[jimbobakoo]

I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.

So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.

This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)

IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)

[jimbobakoo]

I have looked around this forum and it has helped me with so much in the past. However, I don't seem to able to find a resolution to this anywhere, the closest I could find was:

http://www.excelforum.com/excel-gene...ific-data.html

Which also sent me to

http://www.excelforum.com/excel-prog...-workbook.html

I just couldn't get it to work for my particular problem.

[AB33]

"So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1"
What happens to the rows in column M of each sheet when copy the entire row from result sheet?
The columns in sheet result are from B-S, so when copy these columns what happens to the column M in each sheet.
I am not sure?

[jimbobakoo]

I noticed that issue so I moved all the regions into a separate sheet labelled regions, so if the name is found in column B then print to R1 if its found in column D then print to R2. Currently trying to work on a Loop until macro, but still having difficulty.

Either that or if its easier you can just move the region table across a few columns.

[AB33]

The attached is based on overwriting the data in column M if there is a match. If there is not match, the row remains the same.
Once I Know your request, I will adjust the code to reflect your new request

[jimbobakoo]

Or if you still want to use the "If .Name <> "Results" Then" then you can just move the regions over into column U or something

[jimbobakoo]

Ok fantastic start, like I said in my previous post it might be better to just have all of the regions in a separate tab (I believe it should be set out that way in the second document I linked). The only issue I have with the one that you linked is the blank rows, it would be much better if they were all together and just flowed on from each other, similar to the data in the first tab.

[AB33]

Try the update code.

[jimbobakoo]

That seems like it works perfectly, just a couple of questions. Will creating new tabs effect the macro at all? and what tabs directly effect the script, is it just results and regions?

[jimbobakoo]

Actually I just noticed an issue with it, the table I supplied you was a much smaller version of the one I'm currently working with. So when I try it on the one I'm currently using each region can only fit 20 rows. I need it to be pretty much limitless, as in keep searching until it hits a blank field. That is why I was trying a do until loop, as the sheet I'm working with has over 100,000 lines of data, some days it might be more some it might be less.

[AB33]

Slightly amended the code. You can add as many columns as you want in sheet region, but you need to create R1--- R100,sheet names and the data format remains unchanged.
I can also amend the code to take in to account creating new sheet names, but it may not be necessary. Try it.
I will be offsite until some time today.

[jimbobakoo]

Slightly amended the code. You can add as many columns as you want in sheet region, but you need to create R1--- R100,

I will never need anymore R tabs and the region tab will remain unchanged. However, there will be other tabs under completely different names which will extract data from the R1-7 tabs.

[AB33]

I do not understand about limit less rows. The code can copy with any number of rows. If your sheet names are different, I will adjust the code, but not now. I need to shoot- off in a minute. Could you also attach a new sample which mimics your actual data.

[jimbobakoo]

The site wont let me because the file size is too big... When I run the code with my data each Rtab only has 20 lines of data, which definitely isnt right as there are nearly 100,000 rows in my results tab.

[jimbobakoo]

AHHHH I see the issue, sorry I should have explained this. There are multiple version of each city name with different answers, I need them to all be displayed in the relevant "R" tab. Currently there seems to only be 1 row of answers per city. Maybe this data will help you as it has repeated entries of cities but with different answers, as that is what I'm working with.

The file size of the spreadsheet I'm working with is 10mb compressed it's still 7mb. I could email it to you if you like... If you're worried about receiving spam (either from me or others who read this post) you could make a temporary email account.

[jimbobakoo]

Ok I have attached a zipped notepad file with the data in, if you copy and paste this into the sheet you sent me then this will give you exactly what I'm working with. When you run the macro you'll notice that there is only 1 line of answers per city instead of it recognising every city in the results page and sorting them into the relevant "R" tabs.

[AB33]

I did send you a PM, but you have not responded yet

[jimbobakoo]

I did send you a PM, but you have not responded yet

Pm'd & emailed you, thanks.