Challenge

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

PPradeep Amarlapudi18 Aug 2023

New function is not supported in my software

new function is supported in latest version ms365

.unique function

.flter function

.sort function

.switch function

1UNIQUE= (array,[by_col],[exactly_once])

returns a list of unique value in a list or range

>by_col - [optional] mow to compare and exact by row = false (default) by coloumn= true

> exactly once - [optional] true= values that occur once

false= all unique values (default)

2FILTER =FILTER(ARRAY,INCLUDE,[IF_EMPTY])

Filter a range or an array

3SORT =sort(array,[sort_index],[sort_order],[by_col]

sort the content of a range or array

sort index- [optional] coloumn index to use for sorting , default is 1

sort order- [optonal]

1 = ascending

-1= descending

default is ascending order by col- [optional]

true= sort by coloumn

false=sort by row default is false

4SWITCH =switch(expression,value1,result1,[default_or_value2,result2])

evaluate one value (called the expression) against a list of values, and returns the result corresponding to the first matching value ) if there is no match an optional default value may be returned

FOR MACROS IN MY EXCEL HAS DISABLED FOR THIS PROCESS AND I TRIED IN THEEXCEL SO I WROTE THE CODES HERE

Sub MultipleConditionalFormattingExample()

Dim MyRange As Range

'Create range object

Set MyRange = Range("A1:DR307511")

'Delete previous conditional formats

MyRange.FormatConditions.Delete

'Add first rule

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _

Formula1:="=100", Formula2:="=150"

MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)

'Add second rule

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=100"

MyRange.FormatConditions(2).Interior.Color = vbBlue

'Add third rule MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=150"

MyRange.FormatConditions(3).Interior.Color = vbYellow

End Sub

Public Sub PDF()

ThisWorkbook.Sheets(Array("Dashboard")).Select

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:="Sample Excel File Saved As PDF", _

Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=True

End Sub

Sub Highlight_Misspelled_Words()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 6

End Sub

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.