Copy data headings in columns and paste them into multiple rows in other columns.

CV_Brett

New Member
Joined
May 21, 2020
Messages
2
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hi All



Only new to the forum and have been fascinated with Excel and its capabilities for some time but, at best, I’m pretty much a mid-range amateur. I know what I want and what it should do but just don’t have the experience and necessary knowledge to get the results I’m after a lot of the time. So I’m hoping the forum will be my saviour so to speak.



Query:



I have 10 Bill of Materials spreadsheets with more to come.

Each sheet has 7 columns A to G.

Each sheet can have up to 2000 rows.

My query relates to columns A, B, C and D only.

Column A title is: SKU Title.

Column B title is: Product Title.

Column C title is: Product SKU.

Column D title is: Product Name.

Column’s C & D start in C2 & D2 with the name of a product as per the naming convention / format shown in example below:



Product SKU
Product Name
CV-BLACK-AND-YELLOW-BASE
Black and Yellow - Base


Each product has a varying number of materials that it is made up i.e. some can have 10, some 14, some 7 etc.

These materials are represented on the required number of rows below each product name, for the relevant product.

The materials are entered under the headings in both column C & D.

The next product name is entered on the immediately following row in columns C & D.

Then that products materials entered and so on and so on until all products and their materials have been entered in columns C & D.



What I want to be able to do by formula or macro is to have the product names from column C copied to Column A and the product names in column D copied to column B.

However, the respective product names need to be copied to each row for each product.

That is for example, if the first product in column C, including the product name and all of its materials occupy C2 to C13, then the product title in C2 must copied to each corresponding row in column A i.e. the product name form C2 will appear in A2 to A 13.

The same would apply to Column D to Column B.



I imagine if every product occupied the same number of rows in the sheet, then this would not be so difficult, but because that is not the case this presents a bigger challenge – well it’s beyond me anyway lol!!

If this can be done at all, then my thinking was the specific rows containing the product names in columns C & D would need to somehow be formula / macro identifiable.

This would then potentially allow Excel to count the number of rows between each product name row, including the product name row itself, on order to determine which rows in columns A & B to copy the respective column headings to.

On that basis I assigning a Heading style 1 designation to all product names in Column C and a Heading style 2 designation to all product names in column D to make them ‘identifiable’.

I then ‘played’ with that and trying formulas but to no avail.



Hopefully someone can provide a formula / macro that can achieve my desired result and save me hours of copying and pasting, thank in advance.
 

Attachments

  • Bill of Materials example.png
    Bill of Materials example.png
    58.3 KB · Views: 3

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.
 

CV_Brett

New Member
Joined
May 21, 2020
Messages
2
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Your post has been reported as being cross posted with another site.

Whilst we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: > (Message Board Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s) ASAP.
Noted, apologies for that
 

Watch MrExcel Video

Forum statistics

Threads
1,128,091
Messages
5,628,617
Members
416,329
Latest member
phxdan79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top