Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
-
To extract a list of unique female clients' names:
=UNIQUE(FILTER(A:A, B:B="Female")) -
To calculate the total income for male clients:
=DSUM(A:D, "Income", B:B="Male") -
To calculate the average income for male clients:
=DAVERAGE(A:D, "Income", B:B="Male") -
To count the number of occurrences of "FLAG_DOCUMENT_2":
=DCOUNTA(E:F, "FLAG_DOCUMENT_2", E:E="Yes") -
To count the number of occurrences of "FLAG_DOCUMENT_3":
=DCOUNTA(E:F, "FLAG_DOCUMENT_3", E:E="Yes") - Sub ApplyConditionalFormatting()
' Apply conditional formatting rules here
' Example: Highlight cells with values greater than 100
With Range("A2:D10") ' Replace with your desired range
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="100"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.Color = RGB(255, 0, 0) ' Red color
End With
' Freeze panes
With ActiveWindow
.SplitColumn = 1 ' Replace with the column number where you want to freeze panes
.SplitRow = 1 ' Replace with the row number where you want to freeze panes
.FreezePanes = True
End With
End Sub - Sub ExportSelectedRangeToPDF()
Dim selectedRange As Range
Set selectedRange = Selection ' Select the desired range before running this macro
Dim filePath As String
filePath = "C:\Path\to\save\pdf\file.pdf" ' Replace with your desired file path and name
selectedRange.ExportAsFixedFormat Type:=xlTypePDF, FileName:=filePath, Quality:=xlQualityStandard
End Sub - Sub CheckSpelling()
Dim ws As Worksheet
Dim rng As Range
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.UsedRange
' Check spelling for the range
rng.CheckSpelling
Next ws
End Sub