Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Need help populating rectangular matrix from row data

Latest post Thu, Aug 28 2014 8:31 AM by Omar. 1 replies.
  • Thu, Aug 28 2014 7:53 AM

    • Stendahl
    • Not Ranked
    • Joined on Thu, Aug 28 2014
    • Posts 1
    • Points 21

    Need help populating rectangular matrix from row data

    I have an Excel spreadsheet table containing 1 row of data for each score submitted by each reviewer of each application. The number of applications is unlimited and, as reflected in the example table below, the number of reviewers submitting a score for each application is variable:

    Application Reviewer Score
    2015-1120 Brown 5
    2015-1120 Hall 2
    2015-1120 Harris 5
    2015-1120 Jackson 6
    2015-1120 Jones 3
    2015-1120 Martin 8
    2015-1120 Miller 4
    2015-1120 Smith 5
    2015-1120 Thompson 6
    2015-1120 Williams 6
    2015-1120 Young 5
    2015-1254 Anderson 8
    2015-1254 Brown 4
    2015-1254 Johnson 9
    2015-1254 Miller 5
    2015-1254 Smith 8
    2015-1254 Taylor 3
    2015-1254 Thomas 4
    2015-1254 Thompson 7
    2015-1254 White 4


    My objective is transform the table above into the rectangular (applications by reviewer scores) table below, with one row for each application and one column for each reviewer, populated with reviewer scores submitted for that application:
    Reviewer Scores
    Applications Anderson Brown Hall Harris Jackson Johnson Jones Martin Miller Smith Taylor Thomas Thompson White Williams Young
    2015-1120   5 2 5 6   3 8 4 5     6   6 5
    2015-1254 
     8 4       9     5 8 3 4 7 4    


    Any guidance?

    Filed under: ,
    • Post Points: 21
  • Thu, Aug 28 2014 8:31 AM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Re: Need help populating rectangular matrix from row data

    With your cursor in the data table, go to Insert/Pivot Table. Create on new tab. Drag the reviewer field to the Column Label box, the Application field to the Row box and the Score field to the Values box. That gets you something that looks just like what you have here.

    Omar Freeman Kitchener, ON

    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.