VBA To Return Matching Row Data From One Sheet To Another

I have attached a worksheet to show an example of raw data in sheet1 and the solution i require in sheet2 from this data.<br><br>The code would go down sheet 1 identifying matching rows (these occur always when the first 3 columns are identical). In the example this first occurs in rows 3 and 4 in sheet1. All Data from the first matching row is returned to sheet2 in 11 columns (a to k), the next row in the match (row 4 in sheet1) is then transferred to sheet 2 starting at column L. The only difference with this 2nd row of the match is that columns a to c need not be transferred from sheet 1 as these columns will always relate to each part of the data match.<br><br>This is probably sounding more complex than it should but the example shows exactly what is required<br><br>The rules is that the first row of the match will always be 11 columns of data sent to sheet2 (a to k) and the other matching row data will always be in blocks of 8 columns as shown, to be placed adjacent until all parts of the match are complete.<br><br>The dimensions need to allow for about 2500 rows in sheet1 and perhaps up to 8 matches sent to sheet 2 which would be (1*11 +7*8 columns =67 columns) this is the most i have ever come across so again there would need to be flexibility on sheet 2 to accommodate this possibility.<br><br>I hope this is easily understood and would be very grateful for a solution.<br><br>Thanks in advance<br>Paul

Is this what you're after ?

Uses column N as a helper column then deletes N at the end.
There's only one macro, Alt+F8 will bring up the macro dialogue then run.

It's a cracking effort NoS

The only thing is it is returning single occurrences along with the matches which i don't need, would there be any way just to return the matches to sheet 2? (ie when more than 1 trainer is in same race)

In the example with only the matches catered for, sheet 2 would not have rows 2 or 6-10 included etc down the sheet.

If you could change the code to filter out the singles and just return the matches to sheet 2 i would be very grateful indeed, apologies if i did not explain exactly what i wanted clearly enough, what you have done is very close though.

Many Thanks
paul

Oooh... missed that part completely

That's fantastic Nos exactly what was required, thank you so much.

Are there any restrictions on the number of rows in sheet1 in the data set it will look down or the number of matches for any given row sent to sheet 2?

Have just tried it again with 1182 rows of data in sheet 1 and it took a few seconds (expected that) but worked a treat.

Thanks very much for this NoS.

Regards
paul

You're welcome Paul, glad it works for you.

Should work with any number of sheet1 rows.
I'm sure the execution time could be reduced using something that would eliminate the .copy, but that's (currently) beyond me.

Enjoy
NoS