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

NOTE---I am informing acttualy,i am using excel version 2010 that data is very huge amount ,my excel is hanged & system shotdown on the performing time ,I am try to fomating till the sumbition time .so, I am given theoritical answer 

Sir,please accept my theoritical answer.

Thank's   

Gender Ratio of Applicants=(select whole data>create pivot table>select gender in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart)

Car Owner/ Non-Owner Ratio

 Car Owner/ Non-OwnerRatio of Applicants=(select whole data>create pivot table>select car owner in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart

• House Owner/ Non-Owner Ratio

 Owner Realty(House)/ Non-Owner Ratio of Applicants

(select whole data>create pivot table>select House owner  Realty in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart)

Client Income Type

(select whole data>create pivot table>select income type in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart)

Client’s Level of Education

(select whole data>create pivot table>select Nmae education type in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart

• Client’s Housing Type

(select whole data>create pivot table>select Name House type in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart

in row as well vallues column)

Client’s Family Status

(select whole data>create pivot table>select Name _Family_status in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart

Client’s Occupation Type

(select whole data>create pivot table>select Occupation type in row as well vallues column)

select pivot table>create pivot chart(apply some fomating like,color changing,bolt,font,style of chart

DESHBORD ia Nothing it is collacation of chart all chart in one sheet

my deshbord is collacation of Sheet(C1,C2,C3,C4,C5,C6,C7,C8)

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.

NOTE MY SYSTEM These funcation is not working

Answer---

=UNIQUE(array,[by_col],[exactly_once])

retunes a list of unique value in the given range or list

extract

by row=false

by col=true

exactly once

true=approx match

false=all unique value

=FILTER(array,include,[if_empty])

filter the range or an array

=SORT(ARRAY,[sort_index],[sort_order],[by_col])

 =SORTBY(array,by array,[sort_order])

SWITCH(expression value,value1,result1,[default _or_value2,resu;t2])

• Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)

ANSWER--=dsum(all data,field,critria)

               =daverage(all data,field,critria)

                = dmax(all data,field,critria)

   all list formula work with the same formate

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

answer-- select the all FLAG_DOCUMENT_2 to 21 data in new sheet>create a critire>apply the dcount formula>(=dcount(select all data,field,critiria

apply dcount formula in all FLAG_DOCUMENT

Other Deliverable

• Create a macro for conditional formatting & freezing panes

go>developer>record micro>view>freeze panes>freeze Top row>(some formating like conditional formatting,color formatting.....ect)

• Create VBA Code to convert selected data into pdf from applicant data

sub pdf()
chdir "file path"
Sheeti.Export As fixedFormate xltypePDF.fileName
"filepath\REciept.Pdf"openafterpublish=true
End Sub

Check Spelling mistakes in the workbook using VBA Code

Public Sub Spelling_error()
Dim i As Range
For Each i In ActiveSheet.UsedRange
If Not Application.CheckSpelling(i.Text) Then
i.Interior.Color = RGB(225, 0, 0)
End If
Next i
End Sub

https://docs.google.com/spreadsheets/d/1lYP1riYXu9nWd-ZZfeEh3RYoUANYDmmL/edit?usp=drive_link&ouid=102944119618189756785&rtpof=true&sd=true