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

unable to open the attachment

✅ 1. VBA to Apply Conditional Formatting & Freeze Panes

Sub FormatAndFreeze()

With ActiveSheet

' Freeze top row

.Rows("2:2").Select

ActiveWindow.FreezePanes = True

' Apply conditional formatting to highlight income > 50000

.Range("E2:E1000").FormatConditions.Add Type:=1, Operator:=xlGreater, Formula1:="50000"

.Range("E2:E1000").FormatConditions(1).Interior.Color = RGB(255, 255, 0) ' Yellow fill

End With

MsgBox "Formatting and freeze applied!"

End Sub

✅ 2. VBA to Export Selected Range as PDF

Sub ExportToPDF()

Dim exportRange As Range

Dim filePath As String

On Error Resume Next

Set exportRange = Application.InputBox("Select the range to export as PDF", Type:=8)

If exportRange Is Nothing Then Exit Sub

filePath = Application.GetSaveAsFilename( _

InitialFileName:="Applicant_Data", _

FileFilter:="PDF Files (*.pdf), *.pdf", _

Title:="Save as PDF")

If filePath = "False" Then Exit Sub

exportRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath

MsgBox "PDF exported successfully!"

End Sub

✅ 3. VBA to Check Spelling Mistakes in Entire Workbook

Sub CheckSpellingWorkbook()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Cells.CheckSpelling

Next ws

MsgBox "Spelling check completed for entire workbook!"

End Sub

✅ Theoretical Answer for Excel Functions (If NOT Supported in Your Version)

💡 For Female Clients (UNIQUE, SORT, FILTER)

If not supported:

• Use Advanced Filter (Data > Advanced) to extract only Female records.

• Use Pivot Table to summarize Income and Loan Amount.

• You can use helper columns and sort manually if needed.

💡 For Male Clients (DSUM, DAVERAGE, etc.)

Examples:

=DSUM(A1:H100, "Loan Amount", J1:J2) ' Sum of Loan Amount for male

=DAVERAGE(A1:H100, "Income", J1:J2) ' Average Income for male

Where J1:J2 contains criteria like:

Gender

Male

💡 Flag Columns with DCOUNT

You can apply DCOUNT, DCOUNTA, and DCOUNTIF variations:

=DCOUNT(A1:Z100, "FLAG_DOCUMENT_3", J1:J2)

Your basic Loan Dashboard Template, It includes a structured sheet for tracking:

📊 Pivot Chart placeholders for:

• Gender Ratio

• Car/House Ownership

• Income, Education, Housing

• Family & Occupation Status