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

Kindly find the below link for answers.

https://docs.google.com/spreadsheets/d/1_qbqfmwrNH9QQJyt6VzwdowTQqPJ464U/edit?usp=sharing&ouid=105517475586470716426&rtpof=true&sd=true

Q 1. Features of Dashboard - create pivot charts for all below mentioned questions & make an attractive dashboard out of it.

Answer: Dashboard is present in the Worksheet named "Dashboard" which is present in the shared file

Q 2. Separate Worksheet Required for

• Data Summary of All Female Client’s for Income and Loan Amount using New Functions (hint: use unique,sort,filter functions) if These functions are not supporting in your software please give a theoritical answer.
• Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
• Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function

Answer:

1. Data summary of All Female Client is present in the Worksheet named "DATA Summary of Male and Female" which is present in the shared file

2. Data summary of All Male Client is present in the Worksheet named "DATA Summary of Male and Female" which is present in the shared file

3.Data summary of All Flag columns is present in the Worksheet named "DATA Summary of Flag Document" which is present in the shared file

Q 3.Other Deliverables

• Create a macro for conditional formatting & freezing panes
• Create VBA Code to convert selected data into pdf from applicant data
• Check Spelling mistakes in the workbook using VBA Code

Answer: Find below created Macro for conditional formatting & freezing panes and shortcut key is "Ctrl+k"

Sub macro_conditional_formatting_and_freezing_panes()

'

' macro_conditional_formatting_and_freezing_panes Macro

' Uses predefined conditional formatting & freezing panes

'

' Keyboard Shortcut: Ctrl+k

'

Rows("1:1").Select

Selection.AutoFilter

Columns("D:D").Select

With ActiveWindow

.SplitColumn = 0

.SplitRow = 1

End With

ActiveWindow.FreezePanes = True

Selection.FormatConditions.Add Type:=xlTextString, String:="M", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 15773696

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlTextString, String:="F", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 16751103

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Columns("E:F").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="Y", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("F1").Select

Selection.FormatConditions.Delete

Columns("H:H").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=250000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 5287936

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("H1").Select

Selection.FormatConditions.Delete

Columns("I:I").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=500000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 192

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("I1").Select

Selection.FormatConditions.Delete

Columns("J:J").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=20000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 49407

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("J1").Select

Selection.FormatConditions.Delete

Range("P6").Select

ActiveSheet.Range("$A$1:$DR$307512").AutoFilter Field:=13, Criteria1:= _

"=Student", Operator:=xlOr, Criteria2:="=Unemployed"

Columns("M:M").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="Unemployed", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent4

.TintAndShade = 0.399945066682943

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlTextString, String:="Student", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions(1).StopIfTrue = False

ActiveSheet.Range("$A$1:$DR$307512").AutoFilter Field:=13

Range("A1:DR1").Select

Selection.FormatConditions.AddColorScale ColorScaleType:=3

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _

xlConditionValueLowestValue

With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor

.Color = 8109667

.TintAndShade = 0

End With

Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _

xlConditionValuePercentile

Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50

With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor

.Color = 8711167

.TintAndShade = 0

End With

Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _

xlConditionValueHighestValue

With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor

.Color = 7039480

.TintAndShade = 0

End With

Selection.FormatConditions.AddDatabar

Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1)

.MinPoint.Modify newtype:=xlConditionValueAutomaticMin

.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax

End With

With Selection.FormatConditions(1).BarColor

.Color = 8700771

.TintAndShade = 0

End With

Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient

Selection.FormatConditions(1).Direction = xlContext

Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor

Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid

Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _

xlDataBarColor

With Selection.FormatConditions(1).BarBorder.Color

.Color = 8700771

.TintAndShade = 0

End With

Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic

With Selection.FormatConditions(1).AxisColor

.Color = 0

.TintAndShade = 0

End With

With Selection.FormatConditions(1).NegativeBarFormat.Color

.Color = 255

.TintAndShade = 0

End With

With Selection.FormatConditions(1).NegativeBarFormat.BorderColor

.Color = 255

.TintAndShade = 0

End With

ActiveWindow.LargeScroll ToRight:=-1

ActiveWindow.ScrollColumn = 106

ActiveWindow.ScrollColumn = 105

ActiveWindow.ScrollColumn = 83

ActiveWindow.ScrollColumn = 60

ActiveWindow.ScrollColumn = 44

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 1

Columns("C:C").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="Cash loans", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent2

.TintAndShade = 0.799981688894314

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlTextString, String:= _

"Revolving loans", TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions(1).StopIfTrue = False

Columns("E:F").Select

Selection.FormatConditions.Add Type:=xlTextString, String:="N", _

TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 204

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("E1:F1").Select

Selection.FormatConditions.Delete

Columns("H:H").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=250000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent2

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Columns("I:I").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=500000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent3

.TintAndShade = 0.399945066682943

End With

Selection.FormatConditions(1).StopIfTrue = False

Columns("H:H").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=250000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorLight2

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Columns("J:J").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=20000"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorLight2

.TintAndShade = 0.399945066682943

End With

Selection.FormatConditions(1).StopIfTrue = False

Range("K10").Select

ActiveWindow.SmallScroll Down:=0

End Sub

2. Find below VBA code for converting selected data into PDF form from Applicant Data

Sub PrintSelectionToPDF()

Dim Rng As Range

Dim strfile As String

Dim myfile As Variant

If Selection.Count = 1 Then

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

Else

Set Rng = Selection

End If

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

Rng.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

3. Find below VBA code for checking spelling mistakes

Sub HighlightMissspelledCells()

Dim Rng As Range

For Each Rng In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Rng.Text) Then

Rng.Interior.ColorIndex = 3

End If

Next Rng

End Sub