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

Featured Dashboard:

I have created a dashboard in Excel "ALL DATA DASHBOARD", where I have summarized all the data according to the question I have used attractive pivot charts, and different formatting to make it attractive while showcasing the information to the client and I have added Slicers as well where I have formed a report connections so that all the data will get changed based on our requirement in each chart simultaneously and we can even get individual data in every chart according to our needs and client requirements. Kindly review the sheet for reference.

Separate Worksheet:

* Data Summary of All Female Client’s for Income and Loan Amount using New Functions

Ans: Income and loan amount using New functions:

FILTER(A1:C182801, C1:C182801="F")

UNIQUE(FILTER(A1:C182801, C1:C182801="F"))

SORT(UNIQUE(FILTER(A1:C182801, C1:C182801="F")))

Using this functions we can filter out all the values of "F" and after filtering out the value we will use the Unique function to get all the unique value data and sort is using the Sort function.

* Data Summary of All male Client’s for Income and Loan Amount using list Functions

Ans: As I was checking for all the male clients there were all female clients in Excel so I took the DSUM, DCOUNT, DCOUNTA, DMAX, DMIN, DAVERAGE, DSTDEV, DSTDEVP, DVAR, and DVARP. you can change "F" or "M" in the value so that you will get values. Sheet name "NEW LIST FUNCTIONS" in EXCEL

* Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.

Ans: In DCOUNT function we have 2 types DCOUNT and DCOUNTA. I have named a sheet "Flag Documents" in Excel kindly review it.

Link for my Project 2 excel file Drive:

https://docs.google.com/spreadsheets/d/19J9vDEeVvjvkWqn1Sj-svXz-jDQUijKe/edit?usp=sharing&ouid=113711727827915303566&rtpof=true&sd=true

Other Deliverables:

* I have created a Macro for conditional formatting & freezing panes and I have attached a screenshot for it as well and I have attached the file as "Other deliverables". Kindly review.

* Create VBA Code to convert selected data into pdf from applicant data Project 2 :

Public Sub Pdf()

Dim ws As Worksheet

Dim selectedRange As Range

Dim filePath As String

Set ws = ActiveSheet

Set selectedRange = Selection

filePath = Application.DefaultFilePath & "\" & ws.Name & " Selected Data.pdf"

selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

* For Spelling mistake

Public Sub Spelling()

Dim ws As Worksheet

Dim cell As Range

Dim rng As Range

Set ws = ActiveSheet

If Not rng Is Nothing Then

For Each cell In rng

If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then

If Not Application.CheckSpelling(word:=cell.Value) Then

cell.Interior.Color = RGB(255, 0, 0)

End If

End If

Next cell

End If

End Sub