Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: reformatting data to create a table

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there an easy way to reformat data as follows

    Jan Mike 200
    Jan Dave 300
    Jan Jim 400
    Jan Ken 500
    Feb Mike 600
    Feb Dave 800
    Feb Jim 150
    Feb Ken 900

    to

    ________Mike____Dave____Jim____Ken
    Jan_____200_____300_____400____500
    Feb_____600_____800_____150____900

    [ This Message was edited by: mjb on 2002-03-01 05:50 ]

    [ This Message was edited by: mjb on 2002-03-01 05:53 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-01 05:49, mjb wrote:
    Is there an easy way to reformat data as follows

    Jan Mike 200
    Jan Dave 300
    Jan Jim 400
    Jan Ken 500
    Feb Mike 600
    Feb Dave 800
    Feb Jim 150
    Feb Ken 900

    to

    ________Mike____Dave____Jim____Ken
    Jan_____200_____300_____400____500
    Feb_____600_____800_____150____900

    [ This Message was edited by: mjb on 2002-03-01 05:50 ]

    [ This Message was edited by: mjb on 2002-03-01 05:53 ]
    Assuming that that data you posted goes from A2:C9 and the number ARE numbers, in A18 put Jan and copy down i.e. Feb, Mar etc.
    In B17 put your first name and fill the rest in along that row. (these might seem like strange places, it's just where put them to test),
    In b18 put:

    =SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=B$17)*($C$2:$C$8))

    Using the $'s in exactly the same place as above, you should be able to drag right then down this formula.

    Any prob give me a shout.

    Ian Mac

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add column headers to your data list and create a PivotTable.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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