Formatting Excel Data

Discussion in 'General Business' started by vri, Dec 20, 2008.

0
  1. #1
    I have excel data that I need to reformat so that the data is expressed in unique rows. Right now one dimension is in a column and the other dimension is in the first row:

    Ex. This is how the data appears in the spread sheet:

    [​IMG]

    Any links to tutorials on the subject or suggestions would be helpful.

    TIA/VRI

    p.s. seemed to be the appropriate place but please move it it fits better elsewhere.
     
    vri, Dec 20, 2008 IP
  2. SEOBOT

    SEOBOT Banned

    Messages:
    794
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Not able to still understand you friend.
     
    SEOBOT, Dec 20, 2008 IP
  3. internetmarketingiq

    internetmarketingiq Well-Known Member

    Messages:
    3,552
    Likes Received:
    70
    Best Answers:
    0
    Trophy Points:
    165
    #3
    Read Microsofts help file about built in macros. You may spend more time trying to figure out how to do it than to just create a simple macro and cut and paste in the arrangement you desire. This is not a formatting issue, it's data manipulation.
     
    internetmarketingiq, Dec 20, 2008 IP
  4. vri

    vri Peon

    Messages:
    62
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #4
    The data right now is laid out with the width as the first row and the length as the first column. To get the price you go across to the correct width and down to the correct height to determine the price.

    I would like to format the data so that it can be easily imported to Access with a unique record for each price.

    I hope that makes more sense and I will look at the macro's.
     
    vri, Dec 20, 2008 IP
  5. RoyaltyMailDotCom

    RoyaltyMailDotCom Peon

    Messages:
    7
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi Vri,

    Highlight and copy the the horizontal cells, then right click on a cell 'A,B,C etc' and choose option 'paste special'

    At the bottom of that dialog box will be an option to 'Transpose', check that box and click ok.

    All done!

    I hope that helps
     
    RoyaltyMailDotCom, Dec 20, 2008 IP
  6. vri

    vri Peon

    Messages:
    62
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    No luck with the Macro's but the transpose tip has been a real time saver as I wade through the data manually.

    Thanks a bunch RoyalMailDotCom and if anyone else has any experience with automating the process it would be very appreciated.
     
    vri, Dec 22, 2008 IP
  7. ceemage

    ceemage Well-Known Member

    Messages:
    297
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    110
    #7
    1) Go to Sheet2 and put the following formula in cell B2:
    =Sheet1!$A1&";"&Sheet1!B$1&";"&Sheet1!B2
    Code (markup):
    2) Copy this formula until it is as wide and deep as the data in Sheet1.

    3) Copy the cells you have just created and paste-special them into a text editor (or Word!)

    4) Do a search and replace to convert the tabs (^t) into line breaks (^p).

    5) Do a search and replace to convert the semi-colons ( ; ) into tabs (^t)

    6) Copy & paste back into Excel

    A bit convoluted, but if you have loads of data, much better than doing it manually.
     
    ceemage, Dec 22, 2008 IP
  8. ceemage

    ceemage Well-Known Member

    Messages:
    297
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    110
    #8
    Oops! Formula should be:

    =Sheet1!$A[B]2[/B]&";"&Sheet1!B$1&";"&Sheet1!B2
    Code (markup):
    Rest of it as you were.
     
    ceemage, Dec 24, 2008 IP