Pin Me

Microsoft Excel: Create A Summary Of Four Lists

written by: Mr Excel•edited by: Tricia Goss•updated: 11/18/2011

Problem: You have a list of Webelos scouts. As shown in Fig. 804, the list shows who attended various sessions at camp. You need to produce a master list of who attended which session.

  • slide 1 of 2

    Strategy: You can use Data Consolidation to solve this task.

    1) Find a blank section of the worksheet. Enter headings for Name and Class. Copy all of the Aquanaut scouts to the list and assign a value of 1 in the Class column, as shown in Fig. 805.

    2) Copy the Artist scouts below this list and assign a value of 10 in the class column. Copy the Citizen scouts below those with a value of 100. Copy the Engineer scouts below those with a value of 1000. Your list should look like Fig. 806.

    3) Go to a blank section of the worksheet. From the menu, select Data – Consolidate. Choose A1:B21 as the reference. Choose Top Row and Left Column, as shown in Fig. 807. Choose OK.

    Result: As shown in Fig. 808, any scouts with 1111 as the class attended all four sessions. Scouts with 1000 attended only the Engineer session. Scouts with 1 attended only the Aquanaut session

    Additional Details: You might want to assign a numeric format of "0000" to the result, as shown in Fig. 809.

    Summary: Consolidation is one method for identifying who is in which list when you have many lists of data.

    Cross Reference: Pivot tables also do a great job at this task.

    Commands Discussed: Data – Consolidate

  • slide 2 of 2


    Fig. 804Fig. 805Fig. 806Fig. 807Fig. 808Fig. 809