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

https://docs.google.com/spreadsheets/d/1ZFij1W-LGw_bU6VaZdD-n_STI4t8yoiP/edit?usp=drive_link&ouid=105115197374076541633&rtpof=true&sd=true

dashbord

1.i am going to excelsheet and then pivot table pivot chart settingsda for ever one

• Gender Ratio of Applicants
• Car Owner/ Non-Owner Ratio
• House Owner/ Non-Owner Ratio
• Client Income Type
• Client’s Level of Education
• Client’s Housing Type
• Client’s Family Status
• Client’s Occupation Type

Separate Worksheet Required for

1. UNIQUE()

• An array formula allows you to perform multiple calculation at once

or it can perfoms one or more calcuation multiple time itin a selected cell

range.

• UNIQUE(array,[by_col][exactly_once])
• Retuns a list of unique values in a list or range
• by_col-[optional] How to compare and exact. by row=false(default);and column=true.
• exactly_once-[optional] true=values that occur one,false=all unique values (default).

sort()

• SORT(array,[sort_index],[sort_order],[by_col])
• sorts the content of a range or array
• sort_index-[optional] column index to use for sorting.default is 1
• sort.order-[optional]1=ascending,-1=descending.default is ascending order.
• by_col-[optional]true=sort by column. false=sort by row default is false.

FILTER()

• FILTER(array,Inclue,[if_empty])

filter a range or an array.

other deliverables

1.sub Higlight_misspelling_word()

Dm cell As Range

For Each cell In Activesheet.usedRange

if not Application.checkspelling(word:=cell.Text) Then

cell.interior.colorIntex=6

Next

End sub

2.

Sub PDFconversion()
Dim r As Range
Dim s As String

If Selection.Count = 1 Then
Set r = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set r = 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
r.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