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

Please find the link as below,

https://docs.google.com/spreadsheets/d/1HeoJYDEF7eDVDjwQ5C-aM08i4YaJkJSY/edit?usp=share_link&ouid=106260772800668425152&rtpof=true&sd=true

Answer: unique function is used to find out the unique record for each row default works top to the bottom approach, but if we want to find the unique values column-wise, then we need to use an optional argument in the function as by_col = true.

syntex : unique( array,[by_col],[exactly_once])

here array is the range to the selection of data on which we use the unique function in the particular column or row. By default it works row-wise removing duplicate values and only retaining unique values if we needed the unique function to filter the unique value column wise then the optional argument ie: by_col should be set as true or 1. And for the last argument exactly_once which is again optional but by default it has false or 0, if we want to find the true unique records which have not repeated more than once from the selected data range, we should mention exactly_once = true or 1; 

sort function is used to sort the selected data dynamically.

Syntex : sort(array,[sort_index],[sort_order],[by_col])
here array is used to select a range of data to perform the sort function, sort_index is used to select which column performs the sort, sort_order is for ascending or descending sort, by_col is for sorting the data in column order.

The filter function is used to filter out data records.

Syntex : filter(array, include,[if_optinal])
heare array is used to select a range of data to perform the filter function. "inclue" is used for logical test statements for filtering data and "if_optinal" is used to handle the "#CALC" error.

Sub PrintSelectionToPDF()

'SUBROUTINE: PrintSelectionToPDF

Dim ThisRng As Range

Dim strfile As String

Dim myfile As Variant

If Selection.Count = 1 Then

Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)

Else

Set ThisRng = Selection

End If

'Prompt for save location

strfile = "Selection" & "_" _

& Format(Now(), "yyyymmdd_hhmmss") _

& ".pdf"

strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _

(InitialFileName:=strfile, _

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

Title:="Select Folder and File Name to Save as PDF")

If myfile <> "False" Then 'save as PDF

ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=True

Else

MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"

End If

End Sub

Sub HighliteMisspelledCells()

Dim cl As Range

For Each cl In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Word:=cl.Text) Then

cl.Interior.Color = vbRed

End If

Next cl

End Sub

Sub Conditinal_Formatting()
'
' Conditinal_Formatting Macro
' Hilighting the applicants above avrage income & loan values
'

'
Range("Applicant_Data__2[[#Headers],[AMT_INCOME_TOTAL]]").Select
Range(Selection, Selection.End(xlDown)).Select
Columns("H:H").Select
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlAboveAverage
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
Range("Applicant_Data__2[[#Headers],[AMT_INCOME_TOTAL]]").Select
Range(Selection, Selection.End(xlDown)).Select
Columns("I:I").Select
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlAboveAverage
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
End Sub