Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
1. Created charts and created a dashboard using the given questions and attached the dashboard in the excel file named "Project 2" available in the link mentioned below.
Link- https://docs.google.com/spreadsheets/d/1FtCz0mG9Ppr-i4juCbTjNazF8UqWryxL/edit?usp=drive_link&ouid=110417719806450186815&rtpof=true&sd=true
2.Data Summary
(1) =DSUM(A:I,8,DU7:DU8) By using this function we can get the income total for the Gender Female (F) and by providing the "Criteria" as
| CODE_GENDER |
| F |
Here the "Field" is mentioned as "8" as i is the Income_Total the output for total income for female clients using this function is "31588428942"
=DSUM(A:I,9,DU7:DU8) By using this function we can get the Total Credit (Loan Amount) for the Gender Female (F) and by providing the "Criteria" as
| CODE_Gender |
| F |
"A:I" - Database
"9"-Field Containing Credit Column
"DU7:DU8"- Criteria
Here the "Field" is mentioned as "9" as i is the Amount_Credit the output for total credit(Loan amount) for female clients using this function is "120004436385"
Atlast we can check by Filtering the gender option with "F" And Obtaining the sum by selecting the whole Income Column And next the Credit Column which gives the sum in the bottom status bar.
We can also use filter and sort functiions to keep the data to our pattern.
(2) =DSUM(A:I,8,DU7:DU8) By using this function we can get the income total for the Gender Male (M) and by providing the "Criteria" as
| CODE_GENDER |
| M |
Here the "Field" is mentioned as "8" as i is the Income_Total the output for total income for male clients using this function is "20318041019"
=DSUM(A:I,9,DU7:DU8) By using this function we can get the Total Credit (Loan Amount) for the Gender Male (M) and by providing the "Criteria" as
| CODE_Gender |
| M |
"A:I" - Database
"9"-Field Containing Credit Column
"DU7:DU8"- Criteria
Here the "Field" is mentioned as "9" as i is the Amount_Credit the output for total credit(Loan amount) for Male clients using this function is "64201050310.50"
Atlast we can check by Filtering the gender option with "M" And Obtaining the sum by selecting the whole Income Column which gives the sum in the bottom status bar.
(3)=DCOUNT(Database,Field,Criteria) BY using this function we can get the count of the cells contining numerical values and by using
=DCOUNTA(Database,Field,Criteria) By using this we can get the count of cells includes numericals, blank cells and empty cells also
Here
Database=Selecting the range containing flag document columns
Field=selecting the column numbers
criiteria= creating the criteria as counting the flag columns.
Thus we can find out the count of cells in the Flag_Document_columns using these fumctions.
3.Deliverables
VBA(Macro) Codes for Conditional Formatting
Public Sub Conditional_Formatting
Dim My_Range As Range
Set My_Range=Worksheets("Applicant Data").Range("A2:K30")
For Each Cell in My_Range
If Cell.Value Like "Y" Then
Cell.Interior.ColorIndex=3
ElseIf Cell.Value Like "N" Then
Cell.Interior.ColorIndex=4
Else
Cell.Interior.ColorIndex=xlNone
End If
Next
End Sub
VBA(Macro) Code for freeze panes
Public Sub Freeze_Panes()
ActiveSheet.Range("B2").Select
Range("B2").Activate
Activewindow.FreezePanes=True
ActiveWindow.FreezePanes=False
End Sub
VBA Code for Spelling Correction
Public Sub Spelling_Correction()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.CheckSpelling
Next
End Sub
VBA Code to convert selected data into Pdf in Applicant Data
Sub SaveRangeAsPDF()
Dim saveLocation As String
Dim ws As Worksheet
Dim rng As Range
saveLocation = "C:\Users\Aswin\Desktop\PDFs\MyPDF.pdf"
Set ws = ActiveSheet
Set rng = Selection
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
End Sub