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

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.

Answer:

• Create a new worksheet in the Excel workbook.
• Rename the worksheet to "Female Clients Summary".
• Copy the column headers from the original dataset to the new worksheet.
• Use the "Filter" function to filter the original dataset to show only female clients.
• Use the "Unique" function to get a list of unique values for the income and loan amount columns.
• Use the "Sort" function to sort the unique values in ascending or descending order, depending on your preference.
• Use the "SUMIF" function or "AVERAGEIF" function to get the total income or average income for female clients based on the loan amount, respectively.
• Enter the formulas in the appropriate cells and format the cells as needed.

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

Answer:

• Create a new worksheet in the Excel workbook.
• Rename the worksheet to "Male Clients Summary".
• Copy the column headers from the original dataset to the new worksheet.
• Use the "DSUM" function to get the total income for male clients.
• Use the "DAVERAGE" function to get the average income for male clients.
• Enter the formulas in the appropriate cells and format the cells as needed.

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

Answer:

• Create a new worksheet in the Excel workbook.
• Rename the worksheet to "Flag Columns Summary".
• Copy the column headers from the original dataset to the new worksheet.
• Use the "DCOUNT" function to count the number of times a particular flag value appears in each flag column.
• Repeat the previous step for each flag column.
• Enter the formulas in the appropriate cells and format the cells as needed.

Create a macro for conditional formatting & freezing panes

Answer:

Sub FormatAndFreeze()
'Define variables
Dim ws As Worksheet
Dim rng As Range

'Set worksheet and range to format
Set ws = ActiveSheet 'Change this to the name of your worksheet if necessary
Set rng = ws.Range("C3:F10") 'Change this to the range you want to format

'Apply conditional formatting to range
With rng
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="10"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.Color = RGB(255, 0, 0) 'Change the color to your preference
End With

'Freeze panes
With ws
.Range("C4").Select 'Select a cell below the frozen rows/columns
.Range("A3:B3").Select 'Select the top row and left two columns
.Application.ActiveWindow.FreezePanes = True 'Freeze the panes
End With

End Sub

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

Answer:

Sub ExportSelectedDataToPDF()

Dim ws As Worksheet
Dim rng As Range
Dim filePath As String
Dim fileName As String

'Set worksheet and range to export
Set ws = ActiveSheet 'Change this to the name of your worksheet if necessary
Set rng = Selection 'Use the currently selected range

'Get save path and file name from user
filePath = Application.GetSaveAsFilename(InitialFileName:=ws.Name, FileFilter:="PDF Files (*.pdf), *.pdf")

If filePath = "False" Then Exit Sub 'Exit if user cancels the save dialog

fileName = Right(filePath, Len(filePath) - InStrRev(filePath, "\")) 'Extract the file name from the path

'Export range as PDF
With ws.PageSetup
.Orientation = xlPortrait 'Set the orientation to portrait
.PrintArea = rng.Address 'Set the print area to the selected range
End With

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

'Show a message box with the file path and name
MsgBox "The selected data has been exported to " & filePath & ".", vbInformation, "Export Complete"

End Sub

Check Spelling mistakes in the workbook using VBA Code

ANSWER:

Sub CheckSpelling()

Dim ws As Worksheet
Dim cell As Range

For Each ws In ActiveWorkbook.Worksheets
For Each cell In ws.UsedRange
If Not cell.HasFormula Then 'Skip cells with formulas
If Not Application.CheckSpelling(cell.Value) Then
cell.Interior.Color = vbYellow 'Highlight cells with spelling mistakes
End If
End If
Next cell
Next ws

MsgBox "Spelling check complete.", vbInformation, "Spell Check"

End Sub