+ Reply to Thread
Results 1 to 8 of 8

How will I manipulate data in excel?

  1. #1
    Registered User
    Join Date
    01-28-2009
    Location
    Manila,Philippines
    MS-Off Ver
    Excel 2003
    Posts
    5

    Smile How will I manipulate data in excel?

    Dear all,

    Please see below sample data and the output that I need to.

    2001 2002 2003

    Sales £51,840.00 £62,208.00 £74,649.60
    Unit Costs 23,152.50 24,310.13 25,525.63
    Fixed Costs 2,500.00 2,500.00 2,500.00
    Gross Profit 26,187.50 35,397.88 46,623.97
    Tax 13,093.75 17,698.94 23,311.98


    What excel function can I use in order for me to give the output below?

    Base on the data above, output would be:

    Sales Unit Cost Fixed cost Gross Tax

    2002 62,208.00 24,310.13 2,500.00 35,397.88 17,698.94


    Sorry its hard to explain.I also attached excel file to clear to understand.


    Thanks.
    garacious
    Attached Files Attached Files
    Last edited by garacious; 08-07-2009 at 04:13 AM.

  2. #2
    Registered User
    Join Date
    08-06-2009
    Location
    Derby, England
    MS-Off Ver
    Excel 2003; Excel 2007
    Posts
    20

    Re: How will I manipulate data in excel?

    Hi,

    You could select the data, copy, then do a "PASTE SPECIAL" and select "TRANSPOSE"

    Regards,

    Mike

  3. #3
    Registered User
    Join Date
    01-28-2009
    Location
    Manila,Philippines
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How will I manipulate data in excel?

    Hi Mike,

    Thanks for your help. But I encounter an error and it says "The information cannot be pasted because the Copy area and the paste area are not the same size and shape."

    I have a large data having a 350 rows and A-AH columns.

    Same,
    garacious

  4. #4
    Registered User
    Join Date
    08-06-2009
    Location
    Derby, England
    MS-Off Ver
    Excel 2003; Excel 2007
    Posts
    20

    Re: How will I manipulate data in excel?

    Excel 2003 only supports 256 columns so, converting 350 rows is not going to be possible.
    Excel 2007 supports over 16,000 columns though - so it *should* work fine in 2007!

    This also means any other method of transposing the data will hit the same problem, so maybe you need to rethink your requirements - or upgrade Excel!

    Mike

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: How will I manipulate data in excel?

    But I encounter an error and it says "The information cannot be pasted because the Copy area and the paste area are not the same size and shape."
    After selecting the Copy area, select a single cell as the Paste Area and Excel will automatically determine the correct size. When copy large chunks of data this is best approach to avoid mismatching the sizes of Copy/Paste areas.


    Excel 2003 only supports 256 columns so, converting 350 rows is not going to be possible.
    You're not limited by your version of Excel. Columns A:AH = 34 columns so you have plenty of "room" on the worksheet or another sheet if desired.
    Last edited by Palmetto; 08-07-2009 at 07:52 AM.

  6. #6
    Registered User
    Join Date
    08-06-2009
    Location
    Derby, England
    MS-Off Ver
    Excel 2003; Excel 2007
    Posts
    20

    Re: How will I manipulate data in excel?

    Quote Originally Posted by Palmetto View Post
    You're not limited by your version of Excel. Columns A:AH = 34 columns so you have plenty of "room" on the worksheet or another sheet if desired.
    If the number of rows is to become the number of columns and the number of columns becomes the number of rows, then, no, the 34 columns won't have a problem becoming 34 rows, but the 350 rows WILL have a problem becoming 350 columns as excel 2003 IS limited to 256 columns per worksheet.

    Try it out, drag a 1,2,3 chain along all the columns and you will reach 256 and won't be able to go any further - or even go one step further (i did) and try doing a paste special until you reach the point the error message occurs - i can gurantee you it will be with 256 rows!

    Mike

  7. #7
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: How will I manipulate data in excel?

    Say, Mike, could you pass the salt? I like a little flavor when I'm eating humble pie.
    I overlooked - - the fact that this is a data transpose issue.

  8. #8
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: How will I manipulate data in excel?

    Hi
    Here are the formulas you can use there.

    Please Login or Register  to view this content.
    See file..
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

+ 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