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)

 Unique List of Female Clients

In a new column, use the UNIQUE function to create a list of unique female clients. Let's assume you're creating this list in column AA, starting from row 2. Use the formula in cell AA2      

=UNIQUE(B2:B1000)

This formula assumes that column B contains the gender information, and the range B2:B1000 represents the data range.

Filter Female Clients' Data

In a new column, use the filter function to filter the data for female clients. Let's assume you're creating this filtered list in column AB, starting from row 2. Use the formula in cell AB2

=FILTER(A2:E1000, B2:B1000="F")

This formula assumes that column A contains client IDs and columns B to E contain various client information. Adjust the range according to your data

Sort Filtered Data by Income

In a new column, use the SORT function to sort the filtered data by income. Let's assume you're creating this sorted list in column AC, starting from row 2. Use the formula in cell AC2:

=SORT(AB2:AE1000, 4, FALSE)

This formula assumes that columns AB to AE contain the filtered data, and column AE represents the income. Change the column numbers based on your data structure.

Data Summary Table

Create a summary table to display the sorted data. we can have columns for client ID, income, and loan amount. Place this table on a new sheet or location within the same sheet.

 

Data Summary of All male Client’s for Income and Loan Amount using list Functions

Create a Criteria Range

In a new area of your worksheet, set up a criteria range. This range will help define the conditions for selecting male clients' data.

For example, you can create headers in cells D1 and E1 named "Gender" and "Client ID," respectively. Then, in D2, enter "M" to indicate male gender.

Data Summary Table

Create a summary table where you want to display the results. For instance, you can have columns for Client ID, Income, and Loan Amount.

Use List Functions

Here's an example of how you can structure your formulas:

Assuming your data range is from A1 to F1000, and your summary table starts from A10, your formulas might look like this:

In A10 (Client ID)

=DSUM(A1:F1000, "Client ID", D1:E2)

In B10 (Income)

=DAVERAGE(A1:F1000, "Income", D1:E2)

In C10 (Loan Amount)

=DSUM(A1:F1000, "Loan Amount", D1:E2)

In these formulas, A1:F1000 represents your data range, and D1:E2 represents your criteria range.

 

 

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

Assuming your data range is from A1 to E1000, and your summary table starts from A10, your formulas might look like this

Using Individual Flag Columns

In A10 (Flag Document 2)
=DCOUNT(A1:E1000, "FLAG_DOCUMENT_2", G1:H2)

In B10 (Flag Document 3)

=DCOUNT(A1:E1000, "FLAG_DOCUMENT_3", G1:H2)

Similarly, you can continue this pattern for other flag columns

 

https://drive.google.com/file/d/1LVGDxuR0_fec_sReww9y-7ErSTO-WniJ/view?usp=drive_link