+ Reply to Thread
Results 1 to 3 of 3

Copy data headings in columns and paste them into multiple rows in different columns

  1. #1
    Registered User
    Join Date
    05-21-2020
    Location
    Perth Western Australia
    MS-Off Ver
    Office 2013 & 2016
    Posts
    1

    Copy data headings in columns and paste them into multiple rows in different columns

    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:

    C D
    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.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello CV_Brett,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    05-21-2020
    Location
    Perth Western Australia
    MS-Off Ver
    Office 2013 & 2016
    Posts
    1

    Re: Copy data headings in columns and paste them into multiple rows in different columns

    Attached sample sheet
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA copy paste from drop down ( multiple rows and columns ) into second workbook
    By givemeaccessexcel in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-06-2018, 07:30 PM
  2. Replies: 8
    Last Post: 01-17-2018, 10:53 AM
  3. [SOLVED] Copy data from multiple columns and paste into single columns
    By daniel_t in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2015, 11:20 AM
  4. Replies: 9
    Last Post: 12-15-2013, 10:05 PM
  5. [SOLVED] Copy/Paste multiple columns as values based on another columns criteria
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2013, 06:50 AM
  6. [SOLVED] Faster way to copy and paste multiple columns in Sheet1 to corresponding columns in Sheet2
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2013, 12:55 AM
  7. Replies: 2
    Last Post: 02-22-2011, 02:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1