Extract Every Instance of Entry to Another Sheet

You'll see this much better in the sample sheet. I wish to do the following:

In sheet "ThisWeek", I want to look up each entry of Column T (Horse) in sheet "ALL". I then want copy EVERY instance (and the entire row of data) of that entry into sheet "Extracted." Now, I gave just a smattering of the info. The real sheets go up to column JF. I'm not sure that matters. And yes the sheets have a header so the search/paste will have to begin at A2. Also, I want the data to pile up of course and not overwrite. Meaning, I could have a list of 100 horses that could return over 1000 matches. I want that list of over 1000 to appear in the Extracted sheet. I hope explained this clearly. I'm tired. Please help!

Are there any duplicate Horse names in Column T of the "ALL" sheet?

Here's one way to attack it:

1. Loop through your list of Horse names in Column T of Sheet "ALL" and autofilter your data in Sheet "ThisWeek" using each horse name as the autofilter criteria.

2. Within the loop.. each time the autofilter gets applied with the next autofilter criteria(horse name).. copy the visible rows after autofilter to the next unused row in your "Extracted" sheet.

Make sense?

Yes, there are duplicate names in the ALL sheet.

I do believe I understand what you're saying. However, I do not have the knowledge to pull that off. LOL Hence...here I am! I know, I know. I'm sorry.

k.. that's no problem.. I would add a step just before step 1 above that goes like:

* Loop through all the Values in Column T and add each Horse name to a Dictionary (Scripting Dictionary) and then you can use each Key of that Dictionary .. (which will be a Unique value (horse name)) as your autofilter criteria in that loop i mentioned..

Do you need further help with any/all or none of the plan of attack i am suggesting?

Edit: k. just saw your last post.. I will, time permitting, make something now..

I'm trying to pull off something with an IF function. No luck. I could sort it manually after that and get what I want. But....LOL... wellllllllllllll

Hi..

Try this..

Please Login or Register  to view this content.

It works in this instance. But not on the big sheet. The sheet it's from goes up to JF (which is what, 266?). Could that be why it doesn't work?

Yeap.. I made it work for the Workbook you attached.. you need to adapt it to your own Workbook.. there's not much that needs changing..

I will make the things that need changing be Red in font below... have a go at adapting it and see how you go..

Please Login or Register  to view this content.

The problem is the file is too big to upload. Maybe the parameters need to tweaked for it go to to JF? It comes up with an error on the big sheet.

ahhhhhhhhhh okay. Thank you. I shall try right now.

You could always upload the file to dropbox or mediafire or similar..

Having said that.. did you look at what I posted in Post #9.. I changed the values(parameters) that need changing to be Bold Red font..

Edit: I see that you see that now..

comes up with this error: Subscript out of range. Here is the debug

Sheets("ThisWeek").AutoFilter.Range.Offset(1, 0).Resize(Sheets("ThisWeek").AutoFilter.Range.Rows.Count - 1, 266).SpecialCells(12).Copy _
Sheets("Extracted").Range("A" & Sheets("Extracted").Range("A" & Rows.Count).End(xlUp).Row + 1)

I do see a mistake on my part. I named the sheet Extract and not Extracted. Be right back

It's running and no error has popped up. So far so good. "ALL" is kind of huge. Almost 30,000 entries. This may take awhile. I didn't shut off conditional formatting either.

I stopped it. It came up with Object Filter of Auto Range Failed.

Method AutoFilter of Object Range Failed.............that's what I get.

This Error came up after I close it down manually. It stops responding. Method Auto Filter of Oject Range Failed

Try the attached.

Go for AB33's solution.. his scripting dictionary and array based solution will be much faster on that amount of data..

I keep trying to post something more but it won't go. Test Test Test

It appears that the site has attacked by virus. I am unable to open page 2 if the thread goes to next page.

AB33, you have it set up to paste as far as column AO. I need it to go as far as JF. Also, you have it to autofit in the columns. I would like it to paste the way it is. And last and far from least........it's not pasting every instance of the horse from this week. It's pasting only the one from ThisWeek. I need each and every instance from ALL (including the one in ThisWeek).

There is auto fit on the code

Please Login or Register  to view this content.

If you do not want it, remove the line.
The code goes as far as any column you wish subject to Excel's columns limit.
If the code stops at column AO, not JF, it is because you have a complete blank column after column AO. I used current region for a range. Current region does not line a complete blank row or column.
I do not understand the other questions you have. You need to attach a desired output.

I made a new worksheet. Here it is. Can you get every instance of it from 'ALL'? Can you also make it so there can be blanks within it?