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

Google drive link

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

VBA code to convert selected data into pdf from applicant data

Sub Printpdf()

Dim Applicant as String

Dim rng as Range

Applicant = "C:\Users\hi\Downloads\Applicant Data.pdf"

Set rng= Sheets("Applicant Data").Range("A1:DR307512")

rng.ExportAsFixedFormat xlTypePDF, Applicant

End Sub

VBA code to check Spelling mistakes in the workbook using VBA Code

Sub Highlight()

Dim rng as Range

For Each rng in ActiveSheet.UsedRange

If NotApplication.CheckSpelling(word:=rng.Text) Then

rng.Style = "Bad"

End If

Next rng

End Sub

Data Summary for all Female Clients

FILTER(A2:DR307512,D2:D307512="F","")

COUNT(FILTER(A2:DR307512,D2:D307512="F",""))

AVERAGE(FILTER(A2:DR307512,D2:D307512="F",""))

MAX(FILTER(A2:DR307512,D2:D307512="F",""))

MIN(FILTER(A2:DR307512,D2:D307512="F",""))

STDEV(FILTER(A2:DR307512,D2:D307512="F",""))

VAR(FILTER(A2:DR307512,D2:D307512="F",""))

Data Summary for male clients

Condition

CODE_GENDER

M

DSUM(A1:DR307512,"AMT_INCOME",Condition)

DAVERAGE(A1:DR307512,"AMT_INCOME",Condition)

DSTDEV(A1:DR307512,"AMT_INCOME",Condition)

DVAR(A1:DR307512,"AMT_INCOME",Condition)

DSUM(A1:DR307512,"AMT_CREDIT",Condition)

DAVERAGE(A1:DR307512,"AMT_CREDIT",Condition)

DSTDEV(A1:DR307512,"AMT_CREDIT",Condition)

DVAR(A1:DR307512,"AMT_CREDIT",Condition)

FLAG_DOCUMENTS

CONDITION1 CONDITION2

FLAG_DOCUMENT1 FLAG_DOCUMENT1

0 1

DCOUNT(A1:DR307512,"FLAG_DOCUMENT1,CONDITION1)

DCOUNT(A1:DR307512,"FLAG_DOCUMENT1,CONDITION2)

DCOUNTA(A1:DR307512,"FLAG_DOCUMENT1,CONDITION1)

DCOUNTA(A1:DR307512,"FLAG_DOCUMENT1,CONDITION2)

similarly for 2 to 21