Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
=UNIQUE(COLUME OF GENDER ,INCOME,LOAN AMOUT COLUMN, FALSE FALSE)
an array formula allows you to perform multiple calculation at once pr it can perform one or calculation multiple time within a selected range cell range
unique(array,(by_col),(exactly _once) return a list of unique value in a list or range), false.
=filter(income of females clients, income of females>25000)
=sort((range of table),1,1) for ascending and -1 for descending
=dsum(databse, criteria,,field)
=daverage(databade, criteria, feild)
=dcount(database, criteria, feild)
- Create a macro for conditional formatting & freezing panes
Sub ApplyConditionalFormattingAndFreezePanes()
' Apply conditional formatting to range A1:B10
With Range("A1:B10").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="20")
.Interior.Color = RGB(255, 0, 0) ' Change to your desired color
End With
' Freeze panes to keep row 2 and column A visible
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
.FreezePanes = True
End With
End Sub
- Create VBA Code to convert selected data into pdf from applicant data
Sub ConvertSelectedRangeToPDF()
Dim selectedRange As Range
Dim exportPath As String
Dim pdfFileName As String
' Check if a range is selected
On Error Resume Next
Set selectedRange = Selection
On Error GoTo 0
If selectedRange Is Nothing Then
MsgBox "Please select a range of data to convert to PDF.", vbExclamation, "No Selection"
Exit Sub
End If
' Set the export path and PDF file name
exportPath = "C:\Path\To\Export\Folder\" ' Change to your desired export folder
pdfFileName = "SelectedData.pdf" ' Change to your desired PDF file name
' Create a new PDF file
selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
exportPath & pdfFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
- Check Spelling mistakes in the workbook using VBA Code
Sub CheckSpellingInWorkbook()
Dim ws As Worksheet
Dim cell As Range
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next ' Skip sheets without text (charts, etc.)
For Each cell In ws.UsedRange
cell.CheckSpelling
Next cell
On Error GoTo 0
Next ws
End Sub
https://docs.google.com/spreadsheets/d/166CUxF1nxLOYbfFbT9SVz58xzIl1S-BQ/edit?usp=sharing&ouid=112461127422444249833&rtpof=true&sd=true