Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
The above link is for excel file.
1. unique and sort function is not working in my excel.
formula for data summary for females for Income and Loan Amount.
=SORT(FILTER(array,range(of code gender)=F(criteria for female),""),sort_index,sort_order)
=sum(range)
sort_order = 1 for ascending, -1 for descending.
2. data summary for males is in sheet 1
syntax is =DSUM(database,filed,criteria)
=DAVERAGE(database,filed,criteria)
=DSUM('Applicant Data'!A1:DS307512,8,Sheet1!B4:B5)
=DAVERAGE('Applicant Data'!A1:DS307512,8,Sheet1!B4:B5)
Create a macro for conditional formatting & freezing panes
Sub ApplyConditionalFormattingAndFreezePanes()
With Range("A1:B10").FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="10")
.Interior.Color = RGB(255, 0, 0)
End With
With ActiveWindow
.SplitColumn = 2
.SplitRow = 1
.FreezePanes = True
End With
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
Create VBA Code to convert selected data into pdf from applicant data
Sub ConvertSelectedDataToPDF()
Dim rng As Range
Dim ws As Worksheet
Dim savePath As String
Set ws = ThisWorkbook.ActiveSheet
Set rng = Selection
savePath = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf")
If savePath <> "False" Then
rng.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath
ActiveWorkbook.Close SaveChanges:=False
MsgBox "Data successfully converted and saved as PDF."
Else
MsgBox "PDF conversion canceled."
End If
End Sub
Check Spelling mistakes in the workbook using VBA Code
Sub HighlightSpellingMistakes()
Dim rng As Range
Dim cell As Range
Set rng = ActiveSheet.UsedRange
rng.Cells.FormatConditions.Delete
For Each cell In rng
If Not Application.CheckSpelling(cell.Value) Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next cell
MsgBox "Spelling mistakes highlighted successfully.", vbInformation
End Sub