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:
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.
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.
