Copy items in column to a row

  • Hello,


    I am trying to copy the items (going down) in a column to a row going accross. Any formula that will do this? I have 20 items in the column that I need to move to a row with out lossing the links.


    Thanks!

  • Re: Copy items in column to a row


    Try PasteSpecial then select Transpose.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Copy items in column to a row


    Thanks, it try that and it works. However, it does not carry over the formula that the item in the column has. I need this function since this column is linked to other items. Any ideas?


    Regards

  • Re: Copy items in column to a row


    What do you mean by does not carry the formula over? Are you talking about the formula in the cells that you are copying/pasting, or formulas referencing the cells you are copying?


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Copy items in column to a row


    For your info there is a Web site that has about everthing you might want for this kind of spreadsheet work. Web address J-walk.com

  • Re: Copy items in column to a row


    So I went and transposed the values as directed earlier. This swaped all just fine for me. A few format changes mine looked like what you want in just a few minutes. All values were moved just fine.


    So do want the move to happen just one time, or the values to carry automatically as infomation is inputed?
    If so you will just have to tell it what each column is equal to for each individual cell. ao2 =k12 and ao3 =l12 and so on. Hope this helps.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Copy items in column to a row


    Afraid I dont see why the Transpose does not work for you. As far as I can tell you have no formulas in your original data. You do have formulas in you output data...Are these the ones you wish to keep?


    As horrible as it sounds I don't think there is a way to transpose the formulas int he way you wish, eg, to drag across and have the formula reference to go down.


    You could do this automatically using VBA, but this will not leave you with the formulas.


    Equally you could use VBA to create the formula for you, as a once off code.


    If you need help setting up any of these code, then post back.


    Hope this helps


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Copy items in column to a row


    Alastair,


    Thanks. Yes that is what I am trying to do. Copy the formula to the right while copying the items on the column below.


    Do you have a VBA code to do this?


    Thanks

  • Re: Copy items in column to a row


    Hi
    try

  • Re: Copy items in column to a row


    JINDON, Alastair


    THANKS!!! It works, this is going to save me a lot of time. I appreciate your time and support!


    I have another question, I am wondering if you can help me... on this post or if I need to start another post??? please let me know.


    Attached is a file where, I am trying to match a country to a supervisor. I have the supervisor per country, now I need to add the supervisor to the country?


    Thanks and Regards

  • Re: Copy items in column to a row


    I don't think this is possible using standard Excel functions (Happy to be proved wrong!), though it may be possible to create a UDF.


    Try posting a separate thread though, this is technically a new question and new topic.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Copy items in column to a row


    This UDF works.



    rng includes the header, and assumes the header is at the top of the data.
    crit in this case is country a etc.


    Hope this helps


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

  • Re: Copy items in column to a row


    try

  • Re: Copy items in column to a row


    JINDON,


    I am getting an error "Compile error" duplicate declaration on current scope


    Dim title, data, result(), x, i, ii. I am not sure how to fix this.


    Thanks and regards

  • Re: Copy items in column to a row


    A UDF (User defined function) works in the same way as a standard Excel function like SUM.


    Put the code into a new module (open VBE, and then go Insert>>Module)



    Then you can use as a normal function. Enter it into a cell as


    =SearchArray(criteria to search for, range to search in (including the headers))


    Hope this helps


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!