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

Features of Dashboard - create pivot charts for all below mentioned questions & make an attractive dashboard out of it.

• 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

ANSWER : Cant attach the file due to its large size and cant upload it in google drive due to some issues so hereby i have submitted the screenshot please kindly consider it .

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.
• Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
• Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.

ANSWER : As mentioned hereby attached a theoritical answer

1. Data Summary of All Female Clients for Income and Loan Amount using New Functions:

   • Create a new worksheet for the data summary.
   • Copy the relevant columns (e.g., "Gender," "Income," and "Loan Amount") from the original dataset into the new worksheet.
   • Use the FILTER function to filter the data to include only female clients based on the "Gender" column.
   • Once the data is filtered, we can use functions like AVERAGE, SUM, COUNT, etc., to calculate the desired summary statistics for the filtered "Income" and "Loan Amount" columns.

2. Data Summary of All Male Clients for Income and Loan Amount using List Functions:

   • Create another new worksheet for this data summary.
   • Copy the relevant columns (e.g., "Gender," "Income," and "Loan Amount") from the original dataset into the new worksheet.
   • Use the DSUM (Database Sum) function or other relevant list functions (e.g., DAVERAGE, DMAX, DMIN) to calculate the summary statistics for male clients. These functions require setting up a criteria range and criteria conditions based on the "Gender" column to extract and calculate the required values.

3. Data Summary for All Flag Columns using DCOUNT Function Variants:

   • Create a separate worksheet for this data summary.
   • Copy the relevant columns (e.g., "Flag Columns" and associated data) from the original dataset into the new worksheet.
   • Use the DCOUNT function (or its variants like DCOUNTA, DCOUNTB, etc.) to count the occurrences of specific criteria in each flag column. Set up the criteria range and criteria conditions to define the count conditions for each flag column separately.

Other Deliverables

• Create a macro for conditional formatting & freezing panes
• Create VBA Code to convert selected data into pdf from applicant data
• Check Spelling mistakes in the workbook using VBA Code

Answer written :

1. Create a macro for conditional formatting and freezing panes:

   • Open the Excel workbook and navigate to the worksheet where we want to apply conditional formatting and freeze panes.
   • Press "Alt + F11" to open the Visual Basic Editor.
   • In the editor, insert a new module by clicking on "Insert" -> "Module."
   • Write the VBA code to apply conditional formatting to the desired range. For example, we can use the "FormatConditions" property to set up conditions and formatting styles.
   • Write additional VBA code to freeze the desired panes. For example, we can use the "FreezePanes" property to specify the rows and columns to freeze.
   • Save the VBA code and close the Visual Basic Editor.
   • we can then run the macro by pressing "Alt + F8," selecting the macro, and clicking "Run."

2. Create VBA code to convert selected data into PDF from applicant data:

   • Open the Excel workbook and navigate to the worksheet containing the data we want to convert to PDF.
   • Press "Alt + F11" to open the Visual Basic Editor.
   • In the editor, insert a new module by clicking on "Insert" -> "Module."
   • Write the VBA code to select the desired data range. For example, we can use the "Range" object to specify the range based on the applicant data.
   • Use the "ExportAsFixedFormat" method to export the selected range as a PDF. Specify the file name, file format, and other relevant parameters.
   • Save the VBA code and close the Visual Basic Editor.
   • we can then run the code by pressing "Alt + F8," selecting the code, and clicking "Run."

3. Check spelling mistakes in the workbook using VBA code:

   • Open the Excel workbook we want to check for spelling mistakes.
   • Press "Alt + F11" to open the Visual Basic Editor.
   • In the editor, insert a new module by clicking on "Insert" -> "Module."
   • Write the VBA code to loop through all the worksheets in the workbook.
   • Inside the loop, use the "CheckSpelling" method to check the spelling in each worksheet. we can specify the range or leave it empty to check the entire worksheet.
   • Optionally, we can customize the code to handle specific ranges or ignore specific cells/columns if needed.
   • Save the VBA code and close the Visual Basic Editor.
   • we can then run the code by pressing "Alt + F8," selecting the code, and clicking "Run."