Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Please check the below-shared link for MS_Excel project 2. Please let me know if it's not working. Also below attached is an MS Word file for Macro and VBA.
https://drive.google.com/drive/folders/1Mf79nY4L-MJX0IwNmRnx0shRdrhyAybs?usp=sharing
Project 2
You are working as Business Analyst in IJS Bank Limited. Currently your organization is working on Loan Applicant Clients for the current quarter. The Manager of the Branch asked you to analyze Loan Applicant’s data and to Present appropriate report on the shared data. The report should be demonstrated using Dashboard into MS Excel.
Features of Dashboard - create pivot charts for all below mentioned questions & make an attractive dashboard out of it.
- 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
ANS: -
Ms Excel file Attached
Separate Worksheet Required for
- Data Summary of All Female Client’s for Income and Loan Amount using New Functions (hint: use unique,sort,filter functions) if These functions are not supporting in your software please give a theoritical answer.
ANS: -
Ms Excel file Attached
- Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
ANS: -
Ms Excel file Attached
- Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.
ANS: -
Ms Excel file Attached
Other Deliverables
- Create a macro for conditional formatting & freezing panes
Sub ApplyFormattingAndFreezePanes()
Dim ws As Worksheet
Dim rng As Range
Dim conditionRange As Range
' Set the worksheet variable (change "Sheet1" to your sheet's name)
Set ws = ThisWorkbook.Sheets("Sheet1")
' Set the range for conditional formatting (change "A2:D100" to your desired range)
Set conditionRange = ws.Range("A2:D100")
' Apply conditional formatting based on your condition (modify as needed)
With conditionRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C2>100")
.Interior.Color = RGB(255, 0, 0) ' Red background color
End With
' Freeze the top row
ws.Rows("2:2").Select
ws.Activate
ActiveWindow.FreezePanes = True
End Sub
- Create VBA Code to convert selected data into pdf from applicant data
Sub ExportSelectedDataToPDF()
' Define the range containing the applicant data
Dim dataRange As Range
Set dataRange = Selection ' Change this to the actual range containing your data
' Prompt the user for the PDF file path
Dim pdfFilePath As String
pdfFilePath = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save PDF As")
' Check if the user canceled the save operation
If pdfFilePath = "False" Then
Exit Sub
End If
' Create a new PDF file
With CreateObject("Excel.Application")
.SheetsInNewWorkbook = 1
.Worksheets(1).Name = "Applicant Data"
dataRange.Copy .Worksheets("Applicant Data").Range("A1")
.ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFilePath, Quality:=xlQualityStandard
.ActiveWindow.Close False
End With
' Inform the user that the PDF file has been created
MsgBox "PDF file created successfully!", vbInformation
End Sub
- Check Spelling mistakes in the workbook using VBA Code
Sub CheckSpellingInEntireWorkbook()
Dim ws As Worksheet
' Loop through all sheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Activate the sheet before checking spelling
ws.Activate
' Check spelling in the active sheet
ActiveSheet.CheckSpelling
Next ws
End Sub