Open Assignment$6-$DATA Spring $2024.$xlsx

Download Assignment$6-$DATA Spring $2024.$xlsx This workbook has seven worksheets. The data in this workbook is a combined customer, sales, orders, and merchandise information from a home improvement store. There are many duplications and redundancies in this data. You are going to sort and summarize this data for analysis. Identical data is copied to all worksheets except one that is blank.

In $$Sort$$ worksheet, sort records in ascending order by customer last name, then first name, then sale date from newest to oldest. $($Custom Sort tool$)$

In the $$Filter $1$ worksheet, filter the data to show only the records of customers who bought items with a list price of more than $$18$ but whose purchase cost was less than $$15$ after January $20$th$,2015.($Must use Advanced Filter tool$)$

Copy the result of Filter$1$ to a new location $-$ cell Q$7.$

In the $$Filter$2$ worksheet, using the Advanced Filter tool, filter the data to display only the records of customers from both of these two groups:

Living in zip code $50012,$ bought $$Cement$-$Board$-5/8$X$4$X$4$ item in year $2015.$

Living in zip code $68104$ and bought $$Rigid$-$Foam$-2$X$4$X$8$ after May $1,2014,$ through the end of the year.

Copy the results of Filter$2$ to a new location $-$ cell Q$8$

Use the Consolidate$2$ worksheet. Use the consolidate tool to create a list of only "CustomerID" and total sales for each customer in columns $"$S$"$ and $"$T$".$

In the $$Remove Duplicate$$ worksheet, create a non$-$duplicate list of all items sold by using the $$Remove Duplicates$$ tool on the $$Sale ID$$ field.

Create a table in the $$Table$$ worksheet. Include the total row and display the total sales $($PurchaseCost$)$ and the count of items sold $($SaleItem$\_$Quantity$).$ Create two slicers for $$SaleID$$ and $$Description$.$

Assignment $6$ Rubric

Item

Sort: Custom sort works for all $3$ items

Filter$1$: Date filter works as it should

Filter$2$: Date filter works as it should

Consolidate$2$: Both columns were created correctly

Remove duplicate: Unique list of items created

Table worksheet: The table has a total row. Totals of sales and count of items sold are inserted in the totals row. Two slicers are present and work.