Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

1) Features of Dashboard

• Gender Ratio of Applicants
• Car Owner/ Non-Owner Ratio
• House Owner/ Non-Owner Ratio
• Client Income Type
• Client’s Level of Education
• Client’s Housing Type
• Client’s Family Status
• Client’s Occupation Type

Drive link: https://docs.google.com/spreadsheets/d/1_FZmNQpAMq_C-_qKFgMi8w-hOWTyPhnX/edit?usp=share_link&ouid=108112261688735120798&rtpof=true&sd=true

2) Separate Worksheet Required for

• Data Summary of All Female Client’s for Income and Loan Amount using New Functions
• Data Summary of All Male Client’s for Income and Loan Amount using List Functions
• Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function

Drive Link: https://docs.google.com/spreadsheets/d/1kK6UAVqPixx29vgoVkdm9lIGHX2hE8QS/edit?usp=share_link&ouid=108112261688735120798&rtpof=true&sd=true

3)

• Create VBA Code to convert selected data into pdf from applicant data

Ans

Public Sub PDF()
ThisWorkbook.Sheets(Array("Dashboard")).Select

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Sample Excel File Saved As PDF", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

• Check Spelling mistakes in the workbook using VBA Code

Ans

Public Sub Checkword()

Dim abc As String, Result As Boolean
abc = Selection.Value
Result = Application.CheckSpelling(abc)
Selection.Offset(0, 1) = Result

End Sub