Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2012
    Posts
    58
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Can you in VB append column headings to a row and repeat

    Hi All,

    I'm looking for some inspiration

    Does anyone have any ideas how I could join columns heading to a row data in VB and repeat the exercise for each column heading.

    i.e

    I have some column headings in row 5 which run from column F O (These are business units) So F = DEAN, O = BOB etc
    In ROW 7 ROW 325 of columns A,B,C & E I have some data, object account codes and sub ledger type stuff

    What I could like is for the list to be created for each instance of the column headings so I have unique values on a line by line basis:

    DEAN, A,B,C & E created in a new sheet and to repeat this for any data in columns so I should have 318 rows of data which now has DEAN on a line by line basis.

    I would then like this to repeat itself for columns G O creating the same amount of lines and placing them on the existing new sheet we just created and underneath the previous list

    I cant upload files due to my companies security....Grrr

    Thanks

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I think I understand what you want to do.
    In the attached file I created some dummy data, with two named ranges [block1] and [block2].

    Click the button labelled [Process Data] and check if the results are what you expected.

    You can adjust the sheetnames to match those that you are using.

    zeddy
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    stimpsond1 (2013-02-19)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI you can actually do that without code using a consolidation range pivot table. You need to:
    1. Insert a new column E and use a formula like:
    =A2&"|"&B2&"|"&C2&"|"&D2
    to concatenate the key columns into one delimited field.
    2. Create a new pivot table based on consolidation ranges - specify that you will create your own page fields, add the range from the formula column to column P as the consolidation range and choose no page fields, then Finish.
    3. Double-click the Grand Total cell in the created pivot table - this will create a new sheet with all the rows you want.
    4. Use Data-Text to columns to break out the delimited key column into its 4 constituent parts.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Rory

    A pivot table always makes sense of course.
    You can also actually do what was asked without VB by just copying and pasting a lot.
    But then he did start by saying "Can you in VB.."

    I do like different solutions.
    I like yours.

    zeddy

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by zeddy View Post
    But then he did start by saying "Can you in VB.."
    That's true of course but I have found that this is often the case simply because the asker does not know of a reasonable manual method.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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