Automate Department Classification and Visualization

[Remy-Reveltek]

Follow the below instruction to work on this issue.

• Given DataSheet A including 500,000 rows with columns A, B, C, D where: A = File Owner B = Date Created C = Filename D = File Folder

The rows in DataSheet A are not required to be unique and can be duplicate rows.

• Add empty columns to DataSheet A E = Predicted Department Name 1 F = Predicted Department Score % G = Predicted Department Data Retention Policy

• DataSheet B includes columns: A = Department Name B = Comma Separated Values of Possible Department Abbreviations C = Number of 1-10 defining the Department Data Retention Policy to be implement for this department.  

DataSheet B has 20 Unique Rows that each have Unique Background Colors Applied

• Create an Excel Macro to see if Column C and D of DataSet A contains any word from the comma-separated values found in DataSet B, Column B.

If a keyword from Dataset B, Column B is found, then populate DataSheet A, Column E, F, G with the following: E = DataSheet B, Column A F = Percent of Match Found G = DataSheet B, Column C

Update the background color of the row in DataSheet A to match the background of the Department from DataSheet B.

• Create DataSheet C

In DataSheet C, create a Filter Function attached to a Cell A1. Cell A1 is a drop-down to automatically populate the filtered table below to show all rows found in DataSheet A that match the chosen Department from DataSheet B.

This sheet will show all columns from DataSheet A

• Create Business Intelligence Sheet

DataSheet D will be populated with the most relevant data visualizations to help a viewer understand patterns found in the data.

This Sheet will have an assortment of 12 different data visualizations using Charts, graphs, scatter plots, and any other visualization deemed relevant for a manager to accurately optimize their productivity by seeing this Data Sheet.

[bjornmage]

Steps for Completing the Task

Step 1: Add Empty Columns to DataSheet A

Add the following empty columns to DataSheet A:

• E = Predicted Department Name 1
• F = Predicted Department Score %
• G = Predicted Department Data Retention Policy

  Step 2: Review DataSheet B

  DataSheet B includes the following columns:

• A = Department Name
• B = Comma Separated Values of Possible Department Abbreviations
• C = Number of 1-10 defining the Department Data Retention Policy to be implemented for this department. DataSheet B has 20 unique rows that each have unique background colors applied.

  Step 3: Create Excel Macro

  Create an Excel Macro to see if Column C and D of DataSet A contains any word from the comma-separated values found in DataSet B, Column B. If a keyword from Dataset B, Column B is found, then populate DataSheet A, Column E, F, G with the following:

• E = DataSheet B, Column A
• F = Percent of Match Found
• G = DataSheet B, Column C Update the background color of the row in DataSheet A to match the background of the Department from DataSheet B.

  Step 4: Create DataSheet C

  Create DataSheet C. In DataSheet C, create a Filter Function attached to a Cell A1. Cell A1 is a drop-down to automatically populate the filtered table below to show all rows found in DataSheet A that match the chosen Department from DataSheet B. This sheet will show all columns from DataSheet A.

  Step 5: Create Business Intelligence Sheet

  Create DataSheet D. DataSheet D will be populated with the most relevant data visualizations to help a viewer understand patterns found in the data. This Sheet will have an assortment of 12 different data visualizations using Charts, graphs, scatter plots, and any other visualization deemed relevant for a manager to accurately optimize their productivity by seeing this Data Sheet.

[bjornmage]

Automated Department Classification Instructions

To create an Excel workbook with multiple datasheets and a business intelligence sheet, follow these steps:

1. Open Microsoft Excel on your computer.
2. Click on "File" in the top left corner of the screen.
3. Select "New" to create a new workbook.
4. In the "New Workbook" dialog box, select "Blank Workbook" and click "Create."
5. Rename the first sheet "DataSheet A" by double-clicking on the tab at the bottom of the screen and typing in the new name.
6. In DataSheet A, add the following columns:
   • A = File Owner
   • B = Date Created
   • C = Filename
   • D = File Folder
7. Add empty columns to DataSheet A:
   • E = Predicted Department Name 1
   • F = Predicted Department Score %
   • G = Predicted Department Data Retention Policy
8. Rename the second sheet "DataSheet B" by double-clicking on the tab at the bottom of the screen and typing in the new name.
9. In DataSheet B, add the following columns:
   • A = Department Name
   • B = Comma Separated Values of Possible Department Abbreviations
   • C = Number of 1-10 defining the Department Data Retention Policy to be implemented for this department.
10. DataSheet B has 20 Unique Rows that each have Unique Background Colors Applied
11. Rename the third sheet "DataSheet C" by double-clicking on the tab at the bottom of the screen and typing in the new name.
12. In DataSheet C, create a Filter Function attached to a Cell A1. Cell A1 is a drop-down to automatically populate the filtered table below to show all rows found in DataSheet A that match the chosen Department from DataSheet B.
13. This sheet will show all columns from DataSheet A.
14. Rename the fourth sheet "DataSheet D" by double-clicking on the tab at the bottom of the screen and typing in the new name.
15. In DataSheet D, create an assortment of 12 different data visualizations using Charts, graphs, scatter plots, and any other visualization deemed relevant for a manager to accurately optimize their productivity by seeing this Data Sheet. To create an Excel Macro to see if Column C and D of DataSet A contains any word from the comma-separated values found in DataSet B, Column B, follow these steps:
16. Open DataSheet A.
17. Press "Alt + F11" to open the Visual Basic Editor.
18. In the Visual Basic Editor, select "Insert" from the top menu and then select "Module."
19. In the new module, copy and paste the following code:

    Sub FindKeywords()
    Dim cell As Range
    Dim keyword As Variant
    For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    For Each keyword In Split(Sheets("DataSheet B").Range("B2").Value, ",")
        If InStr(cell.Value, keyword) > 0 Then
            cell.Offset(0, 2).Value = Sheets("DataSheet B").Range("A2").Value
            cell.Offset(0, 3).Value = WorksheetFunction.CountIf(cell, keyword) / Len(cell.Value)
            cell.Offset(0, 4).Value = Sheets("DataSheet B").Range("C2").Value
            cell.EntireRow.Interior.Color = Sheets("DataSheet B").Range("A2").Interior.Color
        End If
    Next keyword
    Next cell
    End Sub

20. Save the module and close the Visual Basic Editor.
21. Go back to DataSheet A and select "Developer" from the top menu.
22. Select "Visual Basic" from the "Code" section.
23. Double-click on "ThisWorkbook" in the left-hand pane.
24. Copy and paste the following code:

    Private Sub Workbook_Open()
    Call FindKeywords
    End Sub

25. Save the workbook. These steps will create an Excel workbook with multiple datasheets and a business intelligence sheet, as well as a macro to find keywords in DataSheet A and populate columns E, F, and G with the relevant information from DataSheet B.

[bjornmage]

DataSheet A Sample DataSet:

File Owner	Date Created	Filename	File Folder
John Doe	1/1/2022	Tax_Document_2021_John_Doe.docx	Financials/Taxes/John_Doe
Jane Smith	1/2/2022	Contract_Jane_Smith_2022.xlsx	Legal/Contracts/Jane_Smith
Bob Johnson	1/3/2022	Financial_Records_Bob_Johnson.pptx	Financials/Records/Bob_Johnson
Alice Lee	1/4/2022	Legal_Agreement_Alice_Lee_2022.docx	Legal/Agreements/Alice_Lee
Tom Smith	1/5/2022	Tax_Document_2021_Tom_Smith.xlsx	Financials/Taxes/Tom_Smith
Sarah Jones	1/6/2022	Contract_Sarah_Jones_2022.docx	Legal/Contracts/Sarah_Jones
Mike Brown	1/7/2022	Financial_Records_Mike_Brown.pptx	Financials/Records/Mike_Brown
Emily Davis	1/8/2022	Legal_Agreement_Emily_Davis_2022.xlsx	Legal/Agreements/Emily_Davis
David Kim	1/9/2022	Tax_Document_2021_David_Kim.docx	Financials/Taxes/David_Kim
Grace Lee	1/10/2022	Contract_Grace_Lee_2022.pptx	Legal/Contracts/Grace_Lee
Samuel Park	1/11/2022	Financial_Records_Samuel_Park.xlsx	Financials/Records/Samuel_Park
Julia Kim	1/12/2022	Legal_Agreement_Julia_Kim_2022.docx	Legal/Agreements/Julia_Kim
Chris Lee	1/13/2022	Tax_Document_2021_Chris_Lee.pptx	Financials/Taxes/Chris_Lee
Olivia Kim	1/14/2022	Contract_Olivia_Kim_2022.xlsx	Legal/Contracts/Olivia_Kim
Andrew Park	1/15/2022	Financial_Records_Andrew_Park.docx	Financials/Records/Andrew_Park
Lucy Lee	1/16/2022	Legal_Agreement_Lucy_Lee_2022.pptx	Legal/Agreements/Lucy_Lee
Max Kim	1/17/2022	Tax_Document_2021_Max_Kim.xlsx	Financials/Taxes/Max_Kim
Isabella Park	1/18/2022	Contract_Isabella_Park_2022.docx	Legal/Contracts/Isabella_Park
Jason Lee	1/19/2022	Financial_Records_Jason_Lee.pptx	Financials/Records/Jason_Lee
Sophia Kim	1/20/2022	Legal_Agreement_Sophia_Kim_2022.xlsx	Legal/Agreements/Sophia_Kim
John Doe	5/12/2009	2009_John_Doe_Tax_Document.docx	Financials/Taxes/John_Doe
Jane Smith	7/23/2008	2008_Jane_Smith_Contract.xlsx	Legal/Contracts/Smith-Contract
Bob Johnson	11/4/2014	Bob-Johnson-Financial-Records.pptx	Financials/Records/BobJohnson
Alice Lee	1/19/2016	2016_Alice_Lee_Legal_Agreement.docx	Legal/Agreements/Lee-Agreement
Tom Smith	9/7/2002	2002_Tom_Smith_Tax_Document.xlsx	Financials/Taxes/Smith-Tax
Sarah Jones	8/15/2019	2019_Sarah_Jones_Contract.docx	Legal/Contracts/Jones-Contract
Mike Brown	6/30/2003	MikeBrownFinancialRecords.pptx	Financials/Records/MikeBrown
Emily Davis	2/14/2015	2015_Emily_Davis_Legal_Agreement.xlsx	Legal/Agreements/Davis-Agreement
David Kim	12/1/2017	2017_David_Kim_Tax_Document.docx	Financials/Taxes/Kim-Tax
Grace Lee	3/8/2005	2005_Grace_Lee_Contract.pptx	Legal/Contracts/Lee-Contract
Samuel Park	10/20/2012	SamuelParkFinancialRecords.xlsx	Financials/Records/Park-Records
Julia Kim	4/30/2011	2011_Julia_Kim_Legal_Agreement.docx	Legal/Agreements/Kim-Agreement
Chris Lee	2/9/2018	2018_Chris_Lee_Tax_Document.pptx	Financials/Taxes/Lee-Tax
Olivia Kim	6/17/2006	2006_Olivia_Kim_Contract.xlsx	Legal/Contracts/Kim-Contract
Andrew Park	12/24/2013	AndrewParkFinancialRecords.docx	Financials/Records/Park-Financial
Lucy Lee	9/1/2010	2010_Lucy_Lee_Legal_Agreement.pptx	Legal/Agreements/Lee-Agreement
Max Kim	7/7/2007	2007_Max_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
Isabella Park	11/11/2022	2022_Isabella_Park_Contract.docx	Legal/Contracts/Park-Event
Jason Lee	8/29/2014	Jason-Lee-Financial-Records.pptx	Financials/Records/Lee-Records
Sophia Kim	2/14/2013	2013_Sophia_Kim_Legal_Agreement.xlsx	Legal/Agreements/Kim-Agreement
John Smith	3/5/2004	2004_John_Smith_Tax_Document.xlsx	Financials/Taxes/Smith-Tax
Emma Davis	6/19/2017	2017_Emma_Davis_Contract.docx	Legal/Contracts/Davis-Contract
Alex Johnson	9/30/2015	Alex-Johnson-Financial-Records.xlsx	Financials/Records/Johnson-Records
Lily Lee	4/12/2012	2012_Lily_Lee_Legal_Agreement.pptx	Legal/Agreements/Lee-Agreement
Peter Kim	1/1/2010	2010_Peter_Kim_Tax_Document.docx	Financials/Taxes/Kim-Tax
Rachel Jones	11/22/2018	2018_Rachel_Jones_Contract.xlsx	Legal/Contracts/Jones-Contract
Mark Brown	8/8/2005	MarkBrownFinancialRecords.pptx	Financials/Records/Brown-Records
Sarah Davis	2/14/2019	2019_Sarah_Davis_Legal_Agreement.docx	Legal/Agreements/Davis-Agreement
Kevin Kim	5/5/2011	2011_Kevin_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
Laura Lee	10/10/2008	2008_Laura_Lee_Contract.pptx	Legal/Contracts/Lee-Contract
Michael Park	7/4/2016	MichaelParkFinancialRecords.xlsx	Financials/Records/Park-Records
Ashley Kim	3/20/2014	2014_Ashley_Kim_Legal_Agreement.docx	Legal/Agreements/Kim-Agreement
Daniel Lee	9/15/2010	Daniel-Lee-Financial-Records.pptx	Financials/Records/Lee-Records
Grace Kim	6/30/2017	2017_Grace_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
James Park	12/12/2012	JamesParkFinancialRecords.docx	Financials/Records/Park-Financial
Sophia Lee	5/5/2009	2009_Sophia_Lee_Legal_Agreement.xlsx	Legal/Agreements/Lee-Agreement
David Smith	4/1/2015	2015_David_Smith_Contract.docx	Legal/Contracts/Smith-Contract
Emily Kim	2/14/2008	2008_Emily_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
William Lee	8/18/2011	2011_William_Lee_Contract.pptx	Legal/Contracts/Lee-Contract
Jessica Park	6/1/2016	JessicaParkFinancialRecords.xlsx	Financials/Records/Park-Records
Ryan Kim	9/9/2013	2013_Ryan_Kim_Legal_Agreement.docx	Legal/Agreements/Kim-Agreement
Karen Lee	7/7/2007	2007_Karen_Lee_Contract.xlsx	Legal/Contracts/Lee-Contract
Eric Smith	12/31/2019	2019_Eric_Smith_Tax_Document.docx	Financials/Taxes/Smith-Tax
Samantha Davis	3/15/2014	2014_Samantha_Davis_Contract.xlsx	Legal/Contracts/Davis-Contract
Tyler Johnson	2/14/2015	Tyler-Johnson-Financial-Records.pptx	Financials/Records/Johnson-Records
Ashley Lee	4/20/2010	2010_Ashley_Lee_Legal_Agreement.pptx	Legal/Agreements/Lee-Agreement
Brian Kim	6/6/2006	2006_Brian_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
Nicole Jones	11/11/2011	2011_Nicole_Jones_Contract.docx	Legal/Contracts/Jones-Contract
Ethan Brown	8/8/2008	EthanBrownFinancialRecords.xlsx	Financials/Records/Brown-Records
Lauren Davis	2/14/2012	2012_Lauren_Davis_Legal_Agreement.docx	Legal/Agreements/Davis-Agreement
Tyler Kim	5/5/2015	2015_Tyler_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
Samantha Lee	10/10/2009	2009_Samantha_Lee_Contract.pptx	Legal/Contracts/Lee-Contract
William Park	7/4/2014	WilliamParkFinancialRecords.docx	Financials/Records/Park-Financial
Rachel Kim	3/20/2013	2013_Rachel_Kim_Legal_Agreement.xlsx	Legal/Agreements/Kim-Agreement
Jason Smith	9/15/2010	Jason-Smith-Financial-Records.pptx	Financials/Records/Smith-Records
Sophia Park	6/30/2017	2017_Sophia_Park_Tax_Document.xlsx	Financials/Taxes/Park-Tax
Michael Lee	12/12/2012	Michael-Lee-Legal-Agreement.docx	Legal/Agreements/Lee-Agreement
Emma Smith	4/1/2015	2015_Emma_Smith_Contract.xlsx	Legal/Contracts/Smith-Contract
Ethan Kim	2/14/2008	2008_Ethan_Kim_Tax_Document.xlsx	Financials/Taxes/Kim-Tax
Olivia Lee	8/18/2011	2011_Olivia_Lee_Contract.pptx	Legal/Contracts/Lee-Contract

[bjornmage]

DataSheet B Sample Data

Department Name	Abbreviations	Data Retention Policy
Marketing and Advertising	MKT, MRT, Ad	5
Sales and Business Development	SLS, SAL, BizDev	3
Human Resources and Talent Management	HR, HRS, TM	7
Finance and Accounting	FIN, FNC, Acct	6
Information Technology and Systems	IT, INF, Sys	8
Legal and Regulatory Affairs	LG, LEG, Reg	4
Research and Development	R&D, RND, ResDev	9
Customer Service and Support	CS, CUS, Supp	2
Operations and Supply Chain	OPS, OPN, SC	5
Public Relations and Communications	PR, PUB, Comms	4
Procurement and Purchasing	PC, PRM, Proc	6
Accounting and Financial Reporting	ACC, ACT, FinRep	7
Quality Assurance and Control	QA, QAS, QC	3
Facilities Management	FAC, FCL, FM	2
Administration and Office Management	ADM, ADN, OffMgmt	8
Logistics and Transportation	LGT, LGS, Transp	5
Production and Manufacturing	PDC, PRD, Manuf	9
Security and Safety	SEC, SRY, Safe	4
Training and Development	TRD, TRN, Dev	7
Compliance and Ethics	CPM, CPL, Eth	6
Information Security	InfoSec, IS	8
Product Management	ProdMgmt, PM	9
Data Science and Analytics	DS&A, DataSci	8
Customer Experience	CX, CustExp	2
Marketing Operations	MKTOps, MktgOps	5
Sales Operations	SlsOps, SalesOps	3
Business Intelligence	BI, BusInt	8
Corporate Strategy	CorpStrat, Strat	9
Corporate Development	CorpDev, Dev	7
Corporate Communications	CorpComms, Comms	4
Corporate Social Responsibility	CSR, CorpSocResp	6
Investor Relations	IR, InvRel	4
Treasury	Treas, CashMgmt	6
Tax	Tax, Taxation	7
Internal Audit	IA, IntAud	3
External Audit	EA, ExtAud	4
Risk Management	RM, RiskMgmt	6
Credit	Credit, CreditMgmt	7
Collections	Collections, CollMgmt	5
Mortgage	Mortgage, Mortgages	8
Wealth Management	WM, WealthMgmt	9
Private Banking	PB, PrivBank	7
Investment Banking	IB, InvBank	8
Asset Management	AM, AssetMgmt	6
Real Estate	RE, RealEst	5
Insurance	Ins, Insurance	4
Actuarial	Act, Actuary	7
Claims	Claims, ClaimsMgmt	3
Underwriting	UW, Underwrite	6
Legal Operations	LegalOps, LegOps	4
Regulatory Compliance	RegComp, RegCmpl	6