Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
| Task-1 | Features of Dashboard • 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 |
| Solution | https://drive.google.com/file/d/1irb85pEfvfhAio45Q8QPZdRKHKwNtqQP/view?usp=drive_link (Project -2 dashboard) |
| Task-2 | 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. |
| Solution | Project -2 dashboard--> sheet No.--> Female Client |
| Task-3 | Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..) |
| Solution | Project -2 dashboard--> sheet No.--> Male Client |
| Task-4 | Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function. |
| Solution | Project -2 dashboard--> sheet No.--> Flag Document |
| File Link | https://drive.google.com/file/d/1irb85pEfvfhAio45Q8QPZdRKHKwNtqQP/view?usp=drive_link |
| 1 | Create a macro for conditional formatting & freezing panes |
| Ans. | Sub Color_blank_cells) 'declare variables Dim ws As Worksheet Dim Colorng As Range Set ws = Worksheets"Analysis") Set ColorRng = ws.Range ("B3:C9") 'color blank cells ColorRng.SpecialCells(×ICellTypeBlanks ).Interior.Color = RGB (220, 230, 241) End Sub |
| 2 | Create VBA Code to convert selected data into pdf from applicant data |
| Ans. | Sub CONVERT_PDF() ' ' CONVERT_PDF Macro ' ' Range("C5").Select ChDir "C:\Users\as\Desktop\skill lync\Project-2" Range("A1:D22").Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\as\Desktop\skill lync\Project-2\PROJECT-2 ans.book.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True ActiveWindow.SmallScroll Down:=-132 End Sub |
| 3 | Check Spelling mistakes in the workbook using VBA Code |
| Ans. | Sub higlightmispelledcells() Dim rng As Range For Each rng In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=rng.Text) Then rng.Style = "Bad" End If Next rng End Sub |