Use the text to columns function to parse out the Data $1$ tab. You can use comma as the delimiter.

Data $1$ Tab is complete. All remaining steps will be based on Data $2$ tab.

$2$ Create sum formulas at the bottom of the Gross Sales, Inventory Cost, Cost of Goods Sold, and Profit fields

$3$ You may notice an error or no calculation being done. Remember the format of numbers may come through as text.

Use text to columns to correct $($Please use your judgment for any columns that are showing numbers as text$)$

$4$ Ensure Columns M to Q are formatted as currency and profit margin is formatted as $\%$

$5$ Create a new tab called Summary

$6$ On cell A$1$ of the Summary tab, enter the Unique formula on all customer names from Data $2$

Take a look at the resulting list to find any potential errors, duplicates, or improperly formatted values

$7$ If any changes are needed to the customer names, please change it in the source data $($Data $2$ tab$).$

Remember the trim function may be your best appraoch to change. You can do trim in another column, the copy$/$paste values back into column

$8$ Once you are confident in the list, please copy$/$paste value the list on the Summary, then Sort the list A to Z $($Do not include the header in the sort$)$

$9$ In Column B of the Summary, please complete a SUMIFS for each customer for Gross Sales

$10$ In Column C of the Summary, please complete a SUMIFS for each customer for Cost of Goods Sold

$11$ In Column D of the Summary, please complete a SUMIFS for each customer for Profit

$12$ Ensure each of these newly created columns are formatted as currency. Please add a total to each as well.

$11$ In Column E of the Summary, please create a column called Vlookup City

Using Vlookup, capture the city in the column $($Do not hardcode!$)$

$12$ In Column F of the Summary, please create a column called Xlookup State

Using Vlookup, capture the state in the column $($Do not hardcode!$)$

$13$ Using the Data $2$ tab, please find$/$replace all $"$CO$"$ in the state column with "Colorado"

$14$ In Column G of the summary, please complete a LEFT function, taking the first $2$ characters of the values in A $($Ex: G$2$ takes first $2$ characters of A$2).$ Carry this formula down

$15$ In Column H of the summary, please complete a RIGHT function, taking the last $3$ characters of the values in A $($Ex: H$2$ takes last $3$ characters of A$2).$ Carry this formula down

$16$ Please provide the total sales for Denny's