+ Reply to Thread
Results 1 to 5 of 5

Transposing data to multiple columns

  1. #1
    Registered User
    Join Date
    03-20-2007
    Posts
    4

    Transposing data to multiple columns

    Dear community,

    I need assistance in creating a formula that will transpose data from rows to columns. I want to use some type of formula (vlookup, sumproduct, offset transpose, etc) or any excel function or macro, that would enable me to look up a value in an array and return the values in multiple columns based on the associated client Name and approval/expiration date. I have attached an example of the current and desired output.

    If a company has multiple approvals they will be posted in separate rows. All information will remain the same for a company with the exception of the approval /expiration dates. Ex type of approval,status of approval are the same for each entry. The only info changed are the new approval/expiration dates. Some companies may have 3-4 entries so the columns will need to expand to the right.

    I will copy the data after completion so it does not matter if the new data is on the same or a new output sheet


    Any help would be greatly appreciated.

    Thank you
    sampyth
    Attached Images Attached Images
    Last edited by VBA Noob; 02-18-2009 at 06:07 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Transposing data to multiple columns

    Yes you can do this but it would be far better for all concerned to upload an actual sample file as opposed to an image, else we are obliged to sit and type out data to replicate your setup... which given we do this in our spare time is likely to put most off answering your post.

  3. #3
    Registered User
    Join Date
    03-20-2007
    Posts
    4

    Smile Re: Transposing data to multiple columns

    I have attached a sample file. Thank you for your assistance
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Transposing data to multiple columns

    See the attached -- sheet2 holds output.

    Notes:
    -- 2 helper columns added to Sheet1 such that expensive array formulae can be avoided.
    -- Assumption made per sample that data is sorted by Client and then by Date

    Alternative approach would be to use VBA ... pending volume of data I would not advise use of Arrays in this instance
    (ie removing helpers will necessitate relatively expensive formulae IMO)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-20-2007
    Posts
    4

    Smile Re: Transposing data to multiple columns

    Amazing! It works great!

    Thank you so much for your assistance!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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