Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
1. For creating Data Summary for Female Clients
a. Create a new Work sheet
b. Assign the name in first columns as A1: CODE_GENDER, B1: AMT_INCOME_TOTAL, C1: AMT_CREDIT.
c. In Cell A2, use the Filter Function to extract the data from the source
=FILTER('Applicant Data'!1:1048576,'Applicant Data'!D:D=F)
d. In bottom of the data of AMT_INCOME_TOTAL and AMT_CREDIT calculate the Total by sum function.
=Sum(B2:B307521) and =Sum(C2:C307521)
MACRO
1.
a. Conditional Formatting
Private Sub ApplyConditionalFormatting(ByVal Target As Range)
Dim My_Range As Range
Set My_Range = Worksheets("Sheet1").Range("A1:E20)
For Each Cell In My_Range
If Cell.Value Like "North" Then
Cell.Interior.ColourIndex = 3
Elseif Cell.Value Like "North" Then
Cell.Interior.ColourIndex = 4
Elseif Cell.Value Like "East" Then
Cell.Interior.ColourIndex = 5
Elseif Cell.Value Like "West" Then
Cell.Interior.ColourIndex = 6
Else
Cell.Interior.ColourIndex = xlNone
EndIf
Next
End Sub
b. Freezing Pane
Sub FreezePanes()
' Freeze panes to keep the header row visible
With Sheets("Sheet1")
.Activate
.Rows("2:2").Select ' Adjust the row number as needed
.Application.ActiveWindow.FreezePanes = True
End With
End Sub
2. Convert to PDF
a. Create a Module in Applicant Data Worksheet and below are the coding
Sub ExportSelectedRangeToPDF()
Dim selectedRange As Range
Dim savePath As String
Set selectedRange = Selection
savePath = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save PDF As")
If savePath <> "False" Then
selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, Quality:=xlQualityStandard
End If
End Sub
3. Check Spelling Mistake
Public Sub HighlightSpellingMistakes()
Cells.CheckSpelling SpellLang:=1033
End Sub
Google Drive Link
https://docs.google.com/spreadsheets/d/1NBhR_spF4RR5ChxEWCuMGuZJa915k0eX/edit?usp=sharing&ouid=112167172065816078122&rtpof=true&sd=true