Cycle through list for unique number, copy and paste date x number of times

[AlexPi]

Hello, I am trying to work on a large excel file where I need to cycle through unique ID's and copy specific formula's and text "x" number of rows, to include the unique ID, then the corresponding formula's in the columns to the right. In my specific case, I can copy/paste rows 2 - 37 which includes the text and formula's I need, but I need to find a way to change the unique ID every "x" number of rows (in my case 37) that are copied/pasted, then start on the next unique ID to copy/paste the 37 rows above. The unique ID is used to calculate using a separate worksheet of data in the workbook.

For example,

Unique ID's (will be thousands in total)

100001
100002
100003
100004

I need the unique ID pasted into column F but also paste in specific formula's and text that exist in columns A - CC. In this example, I am showing 3 rows per each unique ID, but I really need 37.

End result would be:

[TABLE="width: 100"]
<tbody>[TR]
[TD]100001[/TD]
[TD]text 1[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Formula 3[/TD]
[/TR]
[TR]
[TD]100001[/TD]
[TD]Text 1[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Formula 3[/TD]
[/TR]
[TR]
[TD]100001[/TD]
[TD]text 1[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Formula 3[/TD]
[/TR]
[TR]
[TD]100002[/TD]
[TD]text 1[/TD]
[TD]Formula 1[/TD]
[TD]Formula 2[/TD]
[TD]Formula 3[/TD]
[/TR]
[TR]
[TD]100002[/TD]
[TD]text 1[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]100002[/TD]
[TD]text 1[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]100003[/TD]
[TD]text 1[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]100003[/TD]
[TD]text 1[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



****** id="cke_pastebin" style="position: absolute; top: 436px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 100"]
<tbody>[TR]
[TD]Formula 1[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[DanteAmor]

To make the cycle I need some data, you could put an example file with fictitious data in the cloud. On sheet1 the original data and on sheet2 the result.

 

[AlexPi]

Thank you for the help. See google drive link below. I am trying to essentially copy rows 2 - 48 and columns A - CC, to row 49. The only difference between each copy/paste range is the unique ID. I am trying to cycle through so it will loop and add a new unique ID to each section of 47 rows. I highlighted the grouping if rows in different colors that can be copy/pasted, just need to plug in the unique ID from the Unique ID list worksheet and cycle through all of them.

https://drive.google.com/drive/folders/1lPU0eyWaq6cA-lvueD1zq7VaaarRfV15?usp=sharing

 

[DanteAmor]

I need permission to download the file. You can grant me permission or check the link to make it public

 

[AlexPi]

Sorry, please try now. Thanks

 

[DanteAmor]

Excuse me but I do not understand where to take the data to fill out the "End result" sheet?

Could you explain step by step what to do with the ID "100001"?
For example:
Step 1, go to "Data table" sheet
Step 2, copy row 2
Step 3, go to the "End Result" sheet
Step 4, paste the copied row 17 times
Step 5, return to "Data table" sheet
Step 6, copy row 3
Step 7, go to the "End Result" sheet
Step 8, paste 17 you see the copied row
Step 9, etc

 

[AlexPi]

Sorry for not being clear.

The highlighted rows on the end result worksheet can all be copied and pasted in blocks of the 47 rows, one after another. Then I need the unique IDs also copied down 47 times in the unique ID column of the end result worksheet.

1) go to end result worksheet and copy rows 2-48, columns A-CC.
2) repeat the copy/paste of the 47 rows for the same number of unique ID’s below each other
3) update the unique ID column on the end result worksheet to include each of the unique IDs, repeated 47 times each

The end result should be 47 rows for each of the unique IDs. The copy/paste will include formulas but can be a simple copy/paste of the range, then the unique ID is updated it will recalculate against a data set (not included on the example)