Question

EX16_XL_CH10_GRADER_ML2_HW - Animal Shelter 1.5

Project Description:

You manage a small animal shelter in Dayton, Ohio. Your assistant created an XML document that lists some of the recent small animals that your shelter took in. In particular, the XML document lists the animal type (such as cat); the age, sex, name, color, and date the animal was brought in; and the date the animal was adopted. You want to manage the data in an Excel worksheet, so you will create a link to the original data source in case they change.

Steps to Perform:

Step	Instructions	Points Possible
1	Download the asset file named e10m2Animals_LastFirst.xml and do not rename it. Open the downloaded workbook named exploring_e10_grader_h3_Animals.xlsx, and save it as exploring_e10_grader_h3_Animals_LastFirst.	0
2	Import the XML document e10m2Animals_LastFirst.xml into Sheet1 of the workbook beginning in cell A1. Rename Sheet1 Animals.	15
3	Create a PivotTable from the imported data on a new worksheet, placing the animal type and sex as row labels (in that order) and counting names as values. Use the adoption date as a report filter and set a filter to show those animals that have not been adopted. Rename the PivotTable worksheet PivotTable.	20
4	Open e10m2Animals_LastFirst.xml in Notepad. Edit the XML document by adding 3/25/2018 for the adoption date for Paws the cat. Edit Fido's data by entering his age: 6 months. Edit Twerpy's color as Orange. Edit Misty's adoption date of 3/31/2018. Save the XML file and close Notepad. Display the Animals worksheet and refresh the connection.	16
5	Display the PivotTable worksheet and refresh the PivotTable.	12
6	On the PivotTable worksheet, type Most Available Animals: in cell A15, Most Adopted Animals: in cell A16, and Cat Gender Most Adopted: in A17. Enter the appropriate answers to these questions in the range B15:B17 (choose from Cats, Dogs, Male, or Female). Resize the columns as necessary. If necessary, reset the filter in the PivotTable to display animals that have not been adopted yet.	18
7	Display the Animals worksheet and type Name, Type as a column label in cell H1. In cell H2, type Paws, Cat. Use Flash Fill to complete the rest of the data entry in this column.	12
8	Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.	7
9	Save the workbook. Ensure that the following worksheets are present (in this order): PivotTable, Animals. Close the workbook, and then submit the file as directed.	0

Total Points

100

<AnimalShelter>

      <Animal>

            <Type>Cat</Type>

            <Age>3 years</Age>

            <Sex>Male</Sex>

            <Name>Paws</Name>

            <Color>White</Color>

            <DateIn>3/15/2018</DateIn>

            <DateAdopted/>

      </Animal>

      <Animal>

            <Type>Cat</Type>

            <Age>8 years</Age>

            <Sex>Female</Sex>

            <Name>Mama Mia</Name>

            <Color>Calico</Color>

            <DateIn>2/15/2018</DateIn>

            <DateAdopted>3/21/2018</DateAdopted>

      </Animal>

      <Animal>

            <Type>Dog</Type>

            <Age>2 years</Age>

            <Sex>Female</Sex>

            <Name>Mrs. Wolf</Name>

            <Color>Brown</Color>

            <DateIn>3/8/2018</DateIn>

            <DateAdopted/>

      </Animal>

      <Animal>

            <Type>Cat</Type>

            <Age>4 months</Age>

            <Sex>Male</Sex>

            <Name>Twerpy</Name>

            <Color/>

            <DateIn>3/12/2018</DateIn>

            <DateAdopted>3/13/2018</DateAdopted>

      </Animal>

      <Animal>

            <Type>Dog</Type>

            <Age>7 months</Age>

            <Sex>Female</Sex>

            <Name>Betsy</Name>

            <Color>Black and White</Color>

            <DateIn>2/25/2018</DateIn>

            <DateAdopted>3/14/2018</DateAdopted>

      </Animal>

      

<Animal>

            <Type>Dog</Type>

            <Age/>

            <Sex>Male</Sex>

            <Name>Fido</Name>

            <Color>Brown and White</Color>

            <DateIn>3/17/2018</DateIn>

            <DateAdopted/>

      </Animal>

      <Animal>

            <Type>Cat</Type>

            <Age>11 months</Age>

            <Sex>Female</Sex>

            <Name>Misty</Name>

            <Color>Black</Color>

            <DateIn>3/10/2018</DateIn>

            <DateAdopted/>

      </Animal>

      <Animal>

            <Type>Cat</Type>

            <Age>5 years</Age>

            <Sex>Male</Sex>

            <Name>Jasper</Name>

            <Color>Black</Color>

            <DateIn>1/31/2018</DateIn>

            <DateAdopted/>

      </Animal>

      <Animal>

            <Type>Cat</Type>

            <Age>12 years</Age>

            <Sex>Female</Sex>

            <Name>Grumpy</Name>

            <Color>Gray</Color>

            <DateIn>2/1/2018</DateIn>

            <DateAdopted>3/18/2018</DateAdopted>

      </Animal>

</AnimalShelter>

Answer 1

Do step 1 and then follow the below procedure

Step 2: To import XML document,

First select cell A1 then go to option Developer >> (In XML section) Import >> Select XML file >> Press OK >> Press OK

You will see the table created from xml file.

Then Right click on "Sheet1" name >> Rename >> rename to "Animals"

Step 3: Create a pivot table in excel

First, select the table range, in this case, select the overall table.

Then go to Insert option >> Click Pivot Table >> click OK

Then On Right hand side you will see 4 options "Filters, Columns, Rows, Values". Now Drag and drop the names into this section.

fields "Types, Sex" will drag to Rows

field "Name" will drag to Values

field "DateAdopted" to Filters

Now to apply filter to show animals that have not been adopted.

click on down arrow button in front of "Date Adopted" cell >> select "blank" >> click OK will filter the record.

To rename the sheet, right click on sheet name at bottom >> select Rename option >> Give "PivotTable"

Step 4: Open the xml file in notepad and update according to given instructions.

Now to reflect the changes in excel file go to Developer option >> in xml block click on "Refresh Data". It will refresh all the data according to the xml file.

Step 5: Go to the PivotTable sheet, click on Data tab >> click on Refresh All (in Queries & Connections)