Alphabetically
Sub SortSheetsAlphabetically()
Dim i As Integer, j As Integer
Dim SheetCount As Integer
SheetCount = ThisWorkbook.Sheets.Count
For i = 1 To SheetCount - 1
For j = i + 1 To SheetCount
If UCase(ThisWorkbook.Sheets(j).Name) < UCase(ThisWorkbook.Sheets(i).Name) Then
ThisWorkbook.Sheets(j).Move Before:=ThisWorkbook.Sheets(i)
End If
Next j
Next i
MsgBox "Sheets have been sorted alphabetically.", vbInformation
End Sub
ListAllSheetNames
Sub ListAllSheetNames()
Dim ws As Worksheet
Dim summarySheet As Worksheet
Dim i As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Delete SheetList if it exists
On Error Resume Next
ThisWorkbook.Worksheets("SheetList").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Create new SheetList sheet
Set summarySheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
summarySheet.Name = "SheetList"
'Add heading
summarySheet.Range("A1").Value = "Sheet Names"
summarySheet.Range("A1").Font.Bold = True
i = 2
'List all worksheet names except SheetList
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> summarySheet.Name Then
summarySheet.Cells(i, 1).Value = ws.Name
i = i + 1
End If
Next ws
summarySheet.Columns("A").AutoFit
Application.ScreenUpdating = True
MsgBox "All sheet names have been listed in 'SheetList'.", vbInformation
End Sub
Year PM Data – Maximum Separation by Name
let
// Load the table
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// List of months with corresponding Person, Min, Max columns
MonthList = {
{"JAN", "Person Name & ID No", "January Minimum", "January Maximum"},
{"FEB", "Person Name & ID No2", "February Minimum", "February Maximum"},
{"MAR", "Person Name & ID No.", "March Minimum", "March Maximum"},
{"APR", "Person Name & ID No.3", "April Minimum", "April Maximum"},
{"MAY", "Person Name & ID No.4", "May Minimum", "May Maximum"},
{"JUN", "Person Name & ID No.5", "June Minimum", "June Maximum"},
{"JUL", "Person Name & ID No.6", "July Minimum", "July Maximum"},
{"AUG", "Person Name & ID No.7", "August Minimum", "August Maximum"},
{"OCT", "Person Name & ID No.8", "October Minimum", "October Maximum"},
{"NOV", "Person Name & ID No.9", "November Minimum", "November Maximum"},
{"DEC", "Person Name & ID No.10", "December Minimum", "December Maximum"}
},
// Convert each month into a table with Person, Type, Value
MonthTables = List.Transform(MonthList, (m) =>
let
t = Table.SelectColumns(Source, {m{1}, m{2}, m{3}}),
renamed = Table.RenameColumns(t, {{m{1},"Person"}, {m{2},"Minimum"}, {m{3},"Maximum"}}),
addMonth = Table.AddColumn(renamed, "Month", each m{0}),
// Unpivot Minimum/Maximum into Type + Value
unpivoted = Table.UnpivotOtherColumns(addMonth, {"Person","Month"}, "Type", "Value")
in
unpivoted
),
// Combine all months
Combined = Table.Combine(MonthTables),
// Remove rows with null Person
NonNullPersons = Table.SelectRows(Combined, each [Person] <> null),
// Pivot Month & Type into columns (JAN Minimum, JAN Maximum, etc.)
AddMonthType = Table.AddColumn(NonNullPersons, "MonthType", each [Month] & " " & [Type], type text),
KeepCols = Table.SelectColumns(AddMonthType, {"Person","MonthType","Value"}),
Pivoted = Table.Pivot(
KeepCols,
List.Distinct(KeepCols[MonthType]),
"MonthType",
"Value",
List.Max
),
// Optional: reorder columns neatly
ColumnOrder = List.Combine(
List.Transform({"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","OCT","NOV","DEC"}, (m) => {m & " Minimum", m & " Maximum"})
),
Reordered = Table.ReorderColumns(Pivoted, List.Combine({{"Person"}, ColumnOrder})),
#"Filtered Rows" = Table.SelectRows(Reordered, each ([Person] = "A. Raju (ID :11538)" or [Person] = "A. Raju(ID: 11538)" or [Person] = "A.Raju (ID :11538)" or [Person] = "A.Raju (ID: 11538)" or [Person] = "A.Raju (ID:11538)"))
in
#"Filtered Rows"
Comments
Post a Comment