copy selection and paste to end of row if 4 criteria are met

[Youngdand]

Hi,

I am struggling with the below problem.

I have a set of data which is 18 columns long and could have many rows.

the data may have many similar items.

For each item where columns a,d,g,h,i,j,k,l,m match another row within the sheet, i need to cut the range a to r to the end of the row containing the first instance, and subsequent matches should be pasted again to the end of this row and so on. below is a small example of the data.

for example row 2 should be pasted to the end of row 1. row 7 should be added to the end of row 6, and row 8 should be added at the end of this.


Can anyone help with this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
Company 1	AB64VBA	Name 1	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 1	AB64VBA	Name 1	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 2	AB64VBA	Name 1	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 2	BC17CLJ	Name 2	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 2	BC17CLJ	Name 2	DiffAdd1	DiffAdd2	DiffAdd3	DiffAdd4	DiffAdd5	DiffPcode	Some info	More info
Company 3	DE57ZTD	Name 3	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 3	DE57ZTD	Name 3	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 3	DE57ZTD <tbody> </tbody>	Name 3	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info
Company 4	EF57ZBC	Name 4	Add1	Add2	Add3	Add4	Add5	Pcode	Some info	More info

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]123456[/TD]
[TD="align: right"]987654[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]27/06/2017[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]25/07/2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]234567[/TD]
[TD="align: right"]876543[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]26/06/2017[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]24/07/2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]345678[/TD]
[TD="align: right"]765432[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]06/07/2017[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]03/08/2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]456789[/TD]
[TD="align: right"]654321[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]05/07/2017[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]02/08/2017[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]567890[/TD]
[TD="align: right"]543210
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]29/06/2017[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]27/07/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]678901[/TD]
[TD="align: right"]432109[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]07/07/2017[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]04/08/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]789012[/TD]
[TD="align: right"]321098
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]13/06/2017[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]11/07/2017[/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"]890123
[/TD]
[TD="align: right"]210987[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]31/08/2017[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]901234
[/TD]
[TD="align: right"]109876[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]06/06/2017[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]04/07/2017[/TD]

</tbody>

Sheet1
​

 

[Youngdand]

HI Fluff,

In the end it threw an Run-time error -2147417878(80010108)
Automation Error
The Object Invoked had disconnected from its clients.

Any idea why this maybe? For reference i am using Excel 2016 64bit

 

[Fluff]

Just tested on ~4500 & it took under a minute. Do you have any event code that might be interfering?
I've no idea why you'd get that error, unless there is something else going on in the background.

 

[Youngdand]

Just tested on ~4500 & it took under a minute. Do you have any event code that might be interfering?
I've no idea why you'd get that error, unless there is something else going on in the background.

After a restart and copying the data to a new workbook, it worked flawlessly and fast. Not sure which fixed it, but it works perfectly.

Thanks a million.

 

[Fluff]

Glad to help & thanks for the feedback