VBA Code to Copy Data From Last Row Based on A Value and Past into Blank Column

Okay, I am new to vba and this will be my first project. I have been watching a few videos on YT, but this is still a little confusing to me. I hope someone can assist or give me steps to understand what is needed to be done to accomplish what I need. The only rows that are important are the following “part” numbers. (1, 7 and 71).
• In the column named “Part” I need to search for the number “1” and pull the data in the “Key” Column and paste it to column “1” all the way down until its sees another “1” in column “Part”. If the column has a “0” in it skip it.
• Next I need to search for “Part” no 71 that is between the current “Part” 1” but above the next “Part 1” it locates and copy the data from the “key” column and paste it to column “2” on the rows that match the last digit to column “ID”.
• The last row will always end with a 7 also.
I will attach a finished example of what I am trying to do. This code will run against thousands of rows daily of this same workbook. I hope I didn’t confuse anyone too much.

Many thanks in advance for anyone that can help.

Jgarza0422

MarvinP, thanks for the warm welcome. I think left out a piece of information. This report file gets generated daily ad the data I have to fill in would be done daily also, as this is why I wanted a programmatically way of getting the results I needed. I did review about the Power Query, but this will only be helpful if I had to perform this action once. But thanks for the suggestion. Do you think this is still possible with VBA or is there another way you can suggest?

Jgarza0422

The file are attached to my original post. Thanks.

Thanks I will review tomorrow at work. Greatly appreciated. I just learned about Power Query. Thanks for the insight.

MarvinP, Thanks for showing me this amazing tool. I managed to recreate the file using yours as a guide and I managed to pick up pretty quick. So again, thanks for the direction. Now I need to learn more about this tool.

Jgarza0422