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

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

Link: _{https://docs.google.com/spreadsheets/d/1bpE5pkH-uRA4B-uuKfTbj6jIhD9sUO-y/edit?usp=sharing&ouid=110323969014430757860&rtpof=true&sd=true}

2.Separate Worksheet Required for: Data summary of all male , female clients and all flag columns.

Link:_{https://docs.google.com/spreadsheets/d/1HOxtsYfCEBEa2aV57gyW4fHj8gh7XnLl/edit?usp=sharing&ouid=110323969014430757860&rtpof=true&sd=true}

3.Other Deliverables:

Create a macro for conditional formatting & freezing panes:

<sup>Sub ApplyFormattingAndFreezePanes()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Loan Applicant's Data")
With ws.Range("H2:K307512").FormatConditions
.Delete
.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="1293502.5"
With .Item(.Count).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With

.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="112500"
With .Item(.Count).Interior
.PatternColorIndex = xlAutomatic
.Color = 16711680
.TintAndShade = 0
End With
End With
ws.Rows("2:2").Select
ActiveWindow.FreezePanes = True
End Sub</sup>

• VBA Code to convert Selected data in to pdf from applicant data

Sub ExportToPDF()

Dim ws As Worksheet

Dim rng As Range

Dim pdfFileName As String

Set ws = ActiveSheet

Set rng = Selection

pdfFileName = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save As PDF")

If pdfFileName = "False" Then

Exit Sub

End If

With rng

.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End With

End Sub

• Check Spelling Mistakes in the Work Book Using VBA Code:

Public Sub Highlight_spellingmistakes()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

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

cell.Interior.ColorIndex = 30

End If

Next cell

End Sub