Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
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)
DATA SUMMARY OF FEMALE CLIENT FEMALE INCOME = 480285841.9 FEMALE CREDIT = 4401870705
- Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
| INCOME | 20318041019 | 193396.482 |
| CREDIT | 64201050311 | 611095.197 |
- Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.
| FLAG_DOCUMENT_2 | FLAG_DOCUMENT_2 | FLAG_DOCUMENT_12 | FLAG_DOCUMENT_12 | |
| 0 | 1 | 0 | 1 | |
| 307498 | 13 | 307509 | 2 | |
| FLAG_DOCUMENT_3 | FLAG_DOCUMENT_3 | FLAG_DOCUMENT_13 | FLAG_DOCUMENT_13 | |
| 0 | 1 | 0 | 1 | |
| 89171 | 218340 | 306427 | 1084 | |
| FLAG_DOCUMENT_4 | FLAG_DOCUMENT_4 | FLAG_DOCUMENT_14 | FLAG_DOCUMENT_14 | |
| 0 | 1 | 0 | 1 | |
| 307486 | 25 | 306608 | 903 | |
| FLAG_DOCUMENT_5 | FLAG_DOCUMENT_5 | FLAG_DOCUMENT_15 | FLAG_DOCUMENT_15 | |
| 0 | 1 | 0 | 1 | |
| 302863 | 4648 | 307139 | 372 | |
| FLAG_DOCUMENT_6 | FLAG_DOCUMENT_6 | FLAG_DOCUMENT_16 | FLAG_DOCUMENT_16 | |
| 0 | 1 | 0 | 1 | |
| 280433 | 27078 | 304458 | 3053 | |
| FLAG_DOCUMENT_7 | FLAG_DOCUMENT_7 | FLAG_DOCUMENT_17 | FLAG_DOCUMENT_17 | |
| 0 | 1 | 0 | 1 | |
| 307452 | 59 | 307429 | 82 | |
| FLAG_DOCUMENT_8 | FLAG_DOCUMENT_8 | FLAG_DOCUMENT_18 | FLAG_DOCUMENT_18 | |
| 0 | 1 | 0 | 1 | |
| 282487 | 25024 | 305011 | 2500 | |
| FLAG_DOCUMENT_9 | FLAG_DOCUMENT_9 | FLAG_DOCUMENT_19 | FLAG_DOCUMENT_19 | |
| 0 | 1 | 0 | 1 | |
| 306313 | 1198 | 307328 | 183 | |
| FLAG_DOCUMENT_10 | FLAG_DOCUMENT_10 | FLAG_DOCUMENT_20 | FLAG_DOCUMENT_20 | |
| 0 | 1 | 0 | 1 | |
| 307504 | 7 | 307355 | 156 | |
| FLAG_DOCUMENT_11 | FLAG_DOCUMENT_11 | FLAG_DOCUMENT_21 | FLAG_DOCUMENT_21 | |
| 0 | 1 | 0 | 1 | |
| 306308 | 1203 | 307408 | 103 |
| FLAG_DOCUMENT_2 | FLAG_DOCUMENT_2 | FLAG_DOCUMENT_12 | FLAG_DOCUMENT_12 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,1,V5:V6) | =DCOUNT(A:T,1,W5:W6) | =DCOUNT(A:T,11,Y5:Y6) | =DCOUNT(A:T,11,Z5:Z6) | |
| FLAG_DOCUMENT_3 | FLAG_DOCUMENT_3 | FLAG_DOCUMENT_13 | FLAG_DOCUMENT_13 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,2,V9:V10) | =DCOUNT(A:T,2,W9:W10) | =DCOUNT(A:T,12,Y9:Y10) | =DCOUNT(A:T,12,Z9:Z10) | |
| FLAG_DOCUMENT_4 | FLAG_DOCUMENT_4 | FLAG_DOCUMENT_14 | FLAG_DOCUMENT_14 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,3,V13:V14) | =DCOUNT(A:T,3,W13:W14) | =DCOUNT(A:T,13,Y13:Y14) | =DCOUNT(A:T,13,Z13:Z14) | |
| FLAG_DOCUMENT_5 | FLAG_DOCUMENT_5 | FLAG_DOCUMENT_15 | FLAG_DOCUMENT_15 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,4,V17:V18) | =DCOUNT(A:T,4,W17:W18) | =DCOUNT(A:T,14,Y17:Y18) | =DCOUNT(A:T,14,Z17:Z18) | |
| FLAG_DOCUMENT_6 | FLAG_DOCUMENT_6 | FLAG_DOCUMENT_16 | FLAG_DOCUMENT_16 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,5,V21:V22) | =DCOUNT(A:T,5,W21:W22) | =DCOUNT(A:T,15,Y21:Y22) | =DCOUNT(A:T,15,Z21:Z22) | |
| FLAG_DOCUMENT_7 | FLAG_DOCUMENT_7 | FLAG_DOCUMENT_17 | FLAG_DOCUMENT_17 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,6,V25:V26) | =DCOUNT(A:T,6,W25:W26) | =DCOUNT(A:T,16,Y25:Y26) | =DCOUNT(A:T,16,Z25:Z26) | |
| FLAG_DOCUMENT_8 | FLAG_DOCUMENT_8 | FLAG_DOCUMENT_18 | FLAG_DOCUMENT_18 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,7,V29:V30) | =DCOUNT(A:T,7,W29:W30) | =DCOUNT(A:T,17,Y29:Y30) | =DCOUNT(A:T,17,Z29:Z30) | |
| FLAG_DOCUMENT_9 | FLAG_DOCUMENT_9 | FLAG_DOCUMENT_19 | FLAG_DOCUMENT_19 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,8,V33:V34) | =DCOUNT(A:T,8,W33:W34) | =DCOUNT(A:T,18,Y33:Y34) | =DCOUNT(A:T,18,Z33:Z34) | |
| FLAG_DOCUMENT_10 | FLAG_DOCUMENT_10 | FLAG_DOCUMENT_20 | FLAG_DOCUMENT_20 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,9,V37:V38) | =DCOUNT(A:T,9,W37:W38) | =DCOUNT(A:T,19,Y37:Y38) | =DCOUNT(A:T,19,Z37:Z38) | |
| FLAG_DOCUMENT_11 | FLAG_DOCUMENT_11 | FLAG_DOCUMENT_21 | FLAG_DOCUMENT_21 | |
| 0 | 1 | 0 | 1 | |
| =DCOUNT(A:T,10,V41:V42) | =DCOUNT(A:T,10,W41:W42) | =DCOUNT(A:T,20,Y41:Y42) | =DCOUNT(A:T,20,Z41:Z42) |
- Create a macro for conditional formatting & freezing panes
Sub MacroCF()
'
' MacroCF Macro
' freeze panels and conditional formatting
'
' Keyboard Shortcut: Ctrl+k
'
ActiveWindow.FreezePanes = True
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 179
ActiveWindow.ScrollColumn = 357
ActiveWindow.ScrollColumn = 179
ActiveWindow.ScrollColumn = 1
Range("Table1[[FLAG_DOCUMENT_2]:[FLAG_DOCUMENT_16]]").Select
ActiveWindow.SmallScroll ToRight:=-9
Range("Table1[[FLAG_DOCUMENT_2]:[FLAG_DOCUMENT_21]]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=-70
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 1
Range("Table1[CODE_GENDER]").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Signs)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Columns("D:D").Select
Range("D2").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range("Table1[FLAG_OWN_CAR]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("Table1[FLAG_OWN_REALTY]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99786370433668E-02
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("G6").Select
ActiveWindow.SmallScroll ToRight:=6
Range("Table1[NAME_INCOME_TYPE]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll ToRight:=2
Range("Table1[NAME_EDUCATION_TYPE]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range("Table1[NAME_FAMILY_STATUS]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("Table1[NAME_HOUSING_TYPE]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll ToRight:=12
Range("Table1[OCCUPATION_TYPE]").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Range("Table1[FLAG_MOBIL]").Select
ActiveWindow.SmallScroll ToRight:=3
Range("Table1[REGION_RATING_CLIENT]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=2
Range("Table1[WEEKDAY_APPR_PROCESS_START]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""MONDAY"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Color = -16383844
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Color = -16383844
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Color = -16383844
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Color = -16383844
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""WEDNESDAY"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""MONDAY"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=2
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""FRIDAY"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
- Create VBA Code to convert selected data into pdf from applicant data
sub pdf()
chdir "file path"
Sheeti.Export As fixedFormate xltypePDF.fileName
"filepath\REciept.Pdf"openafterpublish=true
End Sub
- Check Spelling mistakes in the workbook using VBA Code
Public Sub Spelling_error()
Dim i As Range
For Each i In ActiveSheet.UsedRange
If Not Application.CheckSpelling(i.Text) Then
i.Interior.Color = RGB(225, 0, 0)
End If
Next i
End Sub
Hereby i have attached the drive link
https://drive.google.com/file/d/1ua3HsXhB2AfSgfnyliRvW8iY9QbHAAxC/view?usp=sharing