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

2.

a. If look up value is female clients i.e "f"

suppose lookup value in the cell A1 = f

then =filter(D1:D307512,H1:I307512=A1,"try again")

A

b. if criteria 1 code_gender

2 M

then =Dsum(A1:DR307512,8,A1:A2) for all male clients income

=Dsum(A1:DR307512,9,A1:A2) for all male clients loan amount

c.

=Dcount(A1:DR307512,"FLAG_DOCUMENT_2",criteria) (this applicabe for FLAG_DOCUMENT_3,4,5,.......etc)

3.

Public Sub PrintSelectionToPDF()

'SUBROUTINE: PrintSelectionToPDF

'DEVELOPER: Ryan Wells

'DESCRIPTION: Print your currently selected range to a PDF

Dim ThisRng As Range

Dim strfile As String

Dim myfile As Variant

If Selection.Count = 1 Then

Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)

Else

Set ThisRng = Selection

End If

'Prompt for save location

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 'save as PDF

ThisRng.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

C.

Sub SpellCheckInSpecificSheets()

Worksheets("Spell checking").CheckSpelling

Worksheets(1).CheckSpelling

End Sub