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

Sub DataSummary()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your actual sheet name

' Data Summary for All Female Clients
Dim femaleData As Range
Dim femaleIncome As Range
Dim femaleLoanAmount As Range

' Filter data for female clients based on CODE_GENDER column
Set femaleData = ws.Range("A1").CurrentRegion ' Replace "A1" with the starting cell of your data
femaleData.AutoFilter Field:=4, Criteria1:="F" ' Replace 4 with the column number of CODE_GENDER column

' Get the filtered ranges for income and loan amount columns
Set femaleIncome = femaleData.SpecialCells(xlCellTypeVisible).Columns("H") ' Replace "H" with the column letter of AMT_INCOME_TOTAL
Set femaleLoanAmount = femaleData.SpecialCells(xlCellTypeVisible).Columns("I") ' Replace "I" with the column letter of AMT_CREDIT

' Calculate summary statistics for female clients' income and loan amount
Dim femaleIncomeMean As Double
Dim femaleIncomeMedian As Double
Dim femaleLoanMean As Double
Dim femaleLoanMedian As Double

femaleIncomeMean = Application.WorksheetFunction.Average(femaleIncome)
femaleIncomeMedian = Application.WorksheetFunction.Median(femaleIncome)
femaleLoanMean = Application.WorksheetFunction.Average(femaleLoanAmount)
femaleLoanMedian = Application.WorksheetFunction.Median(femaleLoanAmount)

' Remove the filter
femaleData.AutoFilter

' Print the summary statistics for female clients' income and loan amount
MsgBox "Female Clients' Income Summary:" & vbNewLine & _
"Mean: " & femaleIncomeMean & vbNewLine & _
"Median: " & femaleIncomeMedian & vbNewLine & vbNewLine & _
"Female Clients' Loan Amount Summary:" & vbNewLine & _
"Mean: " & femaleLoanMean & vbNewLine & _
"Median: " & femaleLoanMedian

' Data Summary for All Male Clients
Dim maleData As Range
Dim maleIncome As Range
Dim maleLoanAmount As Range

' Filter data for male clients based on CODE_GENDER column
Set maleData = ws.Range("A1").CurrentRegion ' Replace "A1" with the starting cell of your data
maleData.AutoFilter Field:=4, Criteria1:="M" ' Replace 4 with the column number of CODE_GENDER column

' Get the filtered ranges for income and loan amount columns
Set maleIncome = maleData.SpecialCells(xlCellTypeVisible).Columns("H") ' Replace "H" with the column letter of AMT_INCOME_TOTAL
Set maleLoanAmount = maleData.SpecialCells(xlCellTypeVisible).Columns("I") ' Replace "I" with the column letter of AMT_CREDIT

' Calculate summary statistics for male clients' income and loan amount
Dim maleIncomeMean As Double
Dim maleIncomeMedian As Double
Dim maleLoanMean As Double
Dim maleLoanMedian As Double

maleIncomeMean = Application.WorksheetFunction.Average(maleIncome)
maleIncomeMedian = Application.WorksheetFunction.Median(maleIncome)
maleLoanMean = Application.WorksheetFunction.Average(maleLoanAmount)
maleLoan