Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
GENDER RATIO OF APPLICANTS:
CAR OWNER/NON-OWNER RATIO:
HOUSE OWNER OR NON-OWNER:
CLIENT INCOME TYPE:
CLIENT'S LEVEL OF EDUCATION:
CLIENT'S HOUSING TYPE:
CLIENT'S FAMILY STATUS:
CLIENT'S OCCUPATION TYPE:
THE FUNCTIONS BELOW ARE NOT SUPPORTING SO I GAVE TEORITICAL EXPLAINATION:
client_data = [
{'name': 'Alice', 'gender': 'Female', 'income': 50000, 'loan_amount': 10000},
# Add more clients as needed
]
# Filter female clients
female_clients = filter(lambda client: client['gender'] == 'Female', client_data)
# Get unique values for income and loan amount
unique_incomes = set(client['income'] for client in female_clients)
unique_loan_amounts = set(client['loan_amount'] for client in female_clients)
# Sort the unique values
sorted_unique_incomes = sorted(unique_incomes)
sorted_unique_loan_amounts = sorted(unique_loan_amounts)
# Display the summary
print(f'Unique Incomes of Female Clients: {sorted_unique_incomes}')
print(f'Unique Loan Amounts of Female Clients: {sorted_unique_loan_amounts}')
DATA SUMMARY OF MALES:
=DSUM(D2:D307512,"M",H2:H307512)
=DAVERAGE(D2:D307512,"M",H2:H307512)
=DCOUNT(D2:D307512,"M",H2:H307512)
DATA SUMMARY OF FLAG_DOCUMENTS:
there are 21 flag document columns in the data for every column there are specific data to count below formulas are used for example:
=DCOUNT(CS2:CS307512, "FLAG_DOCUMENT_2",CS1:CS1)
this is the formula for counting number of columns in the FLAG_DOCUMENT_2 .
=DCOUNTA(CS2:CS307512, "FLAG_DOCUMENT_2",CS1:CS1)
this formula counts the number of non-empty cells in the data.
=DCOUNTBLANK(CS2:CS307512, "FLAG_DOCUMENT_2",CS1:CS1)
this formula counts the number of blank cells in the data.
Macro for condiotional formatting and freezing panes:
Sub ApplyConditionalFormattingAndFreezePanes()
' Conditional Formatting
Range("A1:D10").Select ' Replace this with your desired range
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=50"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
' Freezing Panes
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End S
CODE TO CONVERT SELECTED DATA INTO PDF FROM APPLICANT DATA :
Sub ExportSelectedRangeToPDF()
Dim rng As Range
Dim ws As Worksheet
Dim saveLocation As String
' Check if a range is selected
If TypeName(Selection) <> "Range" Then
MsgBox "Please select a range of data to export.", vbExclamation
Exit Sub
End If
' Set the range to export
Set rng = Selection
' Set the worksheet containing the data
Set ws = rng.Worksheet
' Define the save location and file name
saveLocation = "C:\Path\To\Save\Your\File.pdf" ' Change the path and file name as needed
' Export the range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=saveLocation, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
MsgBox "PDF Created Successfully!", vbInformation
End Sub
VBA CODE TO CHECK SPELLING MISTAKES IN THE WORKSHEET:
Sub SpellCheckWorkbook()
Dim ws As Worksheet
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
ws.Activate ' Activate the worksheet
Cells.CheckSpelling ' Perform spell check on the active worksheet
Next ws
MsgBox "Spell check complete.", vbInformation
End Sub