Embedded Files
AI Lessons Learned 2024.pdfDay 1.pdfDay 2.pdfSuccess Stories
Capital One - 50% Technology & 50% Culture
Lesson Learned
Capital One - Distributed Training
Embedded Data Scientists and Analysts: Capital One integrates data scientists and analysts within business units, fostering close collaboration between data experts and business stakeholders.
Citi - Central Congestion
Centralized Data Teams: Citi maintains centralized data teams that manage enterprise-wide data initiatives, ensuring consistency and standardization across the organization.
Success in AI rollout relies on:
CASE 2 Actions vs. Risks
Scan and summarize the following links for common risks regarding AI models taking action.
Case 1.1 AI Models TAKING ACTION:
Generative AI Lawyer: Source 1 Source 2
Case 1.2 AI Models identifying risks for HUMAN ACTION:
McKinsey: Meet Lilli, our generative AI tool that’s a researcher, a time saver, and an inspiration
Excel Pro
Instructions:
Download the Excel file below.
Open it in Excel.
Enable Editing button
Save as a Macro-Enabled Workbook
Press Keyboard keys ALT and F11 at the same time.
Make sure to select the correct file on the left.
Insert > New Module
Copy the VBA Code below in the dropdown, or in your Excel file under the Instructions Tab. (All in one cell). Paste into the New Module window.
Save. Return to the Excel file with the green Excel icon.
Follow the Excel instructions on tab "Instructions".
File #1: Find differences and summarize (Excel VBA)
1 Find differences and summarize - No Macro.xlsxVBA Code for Excel #1
VBA Code for Excel #1
Sub CompareSheetsWithReport()
Dim ws1 As Worksheet, ws2 As Worksheet, wsReport As Worksheet
Dim maxRow As Long, maxCol As Long
Dim r As Long, c As Long
Dim diffCount As Long
Dim reportRow As Long
' Set your sheet names here
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
' Create or clear the report sheet
On Error Resume Next
Set wsReport = ThisWorkbook.Sheets("Differences Report")
If wsReport Is Nothing Then
Set wsReport = ThisWorkbook.Sheets.Add
wsReport.Name = "Differences Report"
Else
wsReport.Cells.Clear
End If
On Error GoTo 0
' Report headers
wsReport.Range("A1:D1").Value = Array("Row", "Column", "Sheet1 Value", "Sheet2 Value")
wsReport.Rows(1).Font.Bold = True
reportRow = 2
' Determine the largest used range
maxRow = Application.WorksheetFunction.Max(ws1.UsedRange.Rows.Count, ws2.UsedRange.Rows.Count)
maxCol = Application.WorksheetFunction.Max(ws1.UsedRange.Columns.Count, ws2.UsedRange.Columns.Count)
diffCount = 0
' Loop through each cell
For r = 1 To maxRow
For c = 1 To maxCol
If ws1.Cells(r, c).Text <> ws2.Cells(r, c).Text Then
diffCount = diffCount + 1
' Highlight differences in original sheets
ws1.Cells(r, c).Interior.Color = vbYellow
ws2.Cells(r, c).Interior.Color = vbYellow
' Add to report
wsReport.Cells(reportRow, 1).Value = r
wsReport.Cells(reportRow, 2).Value = c
wsReport.Cells(reportRow, 3).Value = ws1.Cells(r, c).Text
wsReport.Cells(reportRow, 4).Value = ws2.Cells(r, c).Text
reportRow = reportRow + 1
End If
Next c
Next r
' Autofit report columns
wsReport.Columns("A:D").AutoFit
' Summary message
MsgBox diffCount & " differences found. See 'Differences Report' sheet for details.", vbInformation
End Sub
File #1A: Find differences and summarize rows differ (Excel VBA)
1A Find differences and summarize rows differ no macro.xlsxVBA Code for Excel #1A
VBA Code for Excel #1A
Sub CompareSheetsWithReport()
Dim ws1 As Worksheet, ws2 As Worksheet, wsReport As Worksheet
Dim maxRow As Long, maxCol As Long
Dim r As Long, c As Long
Dim diffCount As Long
Dim reportRow As Long
' Set your sheet names here
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
' Create or clear the report sheet
On Error Resume Next
Set wsReport = ThisWorkbook.Sheets("Differences Report")
If wsReport Is Nothing Then
Set wsReport = ThisWorkbook.Sheets.Add
wsReport.Name = "Differences Report"
Else
wsReport.Cells.Clear
End If
On Error GoTo 0
' Report headers
wsReport.Range("A1:D1").Value = Array("Row", "Column", "Sheet1 Value", "Sheet2 Value")
wsReport.Rows(1).Font.Bold = True
reportRow = 2
' Determine the largest used range
maxRow = Application.WorksheetFunction.Max(ws1.UsedRange.Rows.Count, ws2.UsedRange.Rows.Count)
maxCol = Application.WorksheetFunction.Max(ws1.UsedRange.Columns.Count, ws2.UsedRange.Columns.Count)
diffCount = 0
' Loop through each cell
For r = 1 To maxRow
For c = 1 To maxCol
If ws1.Cells(r, c).Text <> ws2.Cells(r, c).Text Then
diffCount = diffCount + 1
' Highlight differences in original sheets
ws1.Cells(r, c).Interior.Color = vbYellow
ws2.Cells(r, c).Interior.Color = vbYellow
' Add to report
wsReport.Cells(reportRow, 1).Value = r
wsReport.Cells(reportRow, 2).Value = c
wsReport.Cells(reportRow, 3).Value = ws1.Cells(r, c).Text
wsReport.Cells(reportRow, 4).Value = ws2.Cells(r, c).Text
reportRow = reportRow + 1
End If
Next c
Next r
' Autofit report columns
wsReport.Columns("A:D").AutoFit
' Summary message
MsgBox diffCount & " differences found. See 'Differences Report' sheet for details.", vbInformation
End Sub
File #2: Formula Checker (Excel VBA)
2 Formula checking no macros.xlsxVBA Code for Excel #2
VBA Code for Excel #2
Option Explicit
Private Const SUMMARY_SHEET As String = "FormulaCheck"
Private Const HILITE_COLOR As Long = &HE0F3FF ' = RGB(255,243,224)
' Run this after selecting a GOOD reference cell in the sheet
Public Sub Check_Column()
If ActiveCell Is Nothing Then
MsgBox "Select a reference cell (with the correct formula) first.", vbExclamation: Exit Sub
End If
AnalyzeLikeReference ActiveCell, "COLUMN"
End Sub
' Run this after selecting a GOOD reference cell in the sheet
Public Sub Check_Row()
If ActiveCell Is Nothing Then
MsgBox "Select a reference cell (with the correct formula) first.", vbExclamation: Exit Sub
End If
AnalyzeLikeReference ActiveCell, "ROW"
End Sub
' ===== core =====
Private Sub AnalyzeLikeReference(ByVal refCell As Range, ByVal scope As String)
Dim ws As Worksheet: Set ws = refCell.Worksheet
If Not refCell.HasFormula Then
MsgBox "Selected reference " & refCell.Address(False, False) & " has no formula.", vbExclamation
Exit Sub
End If
Dim ur As Range: Set ur = ws.UsedRange
Dim topR As Long, botR As Long, leftC As Long, rightC As Long
topR = ur.Row: botR = ur.Row + ur.Rows.Count - 1
leftC = ur.Column: rightC = ur.Column + ur.Columns.Count - 1
Dim rngScope As Range
If UCase$(scope) = "COLUMN" Then
Set rngScope = ws.Range(ws.Cells(topR, refCell.Column), ws.Cells(botR, refCell.Column))
Else
Set rngScope = ws.Range(ws.Cells(refCell.Row, leftC), ws.Cells(refCell.Row, rightC))
End If
Dim refPat As String: refPat = Normalize(refCell.FormulaR1C1)
Dim wsSum As Worksheet
On Error Resume Next
Set wsSum = ThisWorkbook.Worksheets(SUMMARY_SHEET)
On Error GoTo 0
If wsSum Is Nothing Then
Set wsSum = ThisWorkbook.Worksheets.Add(After:=ws)
wsSum.Name = SUMMARY_SHEET
Else
wsSum.Cells.Clear
End If
With wsSum
.Range("A1").Value = "Formula Check"
.Range("A2").Value = "Reference:"
.Range("B2").Value = ws.Name & "!" & refCell.Address(False, False)
.Range("A3").Value = "Scope:"
.Range("B3").Value = IIf(UCase$(scope) = "COLUMN", _
"Column " & ColLetter(refCell.Column) & " (" & topR & ":" & botR & ")", _
"Row " & refCell.Row & " (" & ColLetter(leftC) & ":" & ColLetter(rightC) & ")")
.Range("A5:E5").Value = Array("Cell", "Status", "Current Formula (preview)", "Reference Formula (preview)", "Current Value")
.Rows(5).Font.Bold = True
End With
Dim outRow As Long: outRow = 6
Dim nHard As Long, nDiff As Long
Dim c As Range
Application.ScreenUpdating = False
For Each c In rngScope.Cells
If c.Address = refCell.Address Then GoTo NextCell
' clear prior highlight from this tool (leave other formatting)
If c.Interior.Color = HILITE_COLOR Then c.Interior.Pattern = xlNone
If Len(CStr(c.Value)) = 0 And Not c.HasFormula Then
' blank: ignore
ElseIf Not c.HasFormula Then
' hardcoded value
c.Interior.Color = HILITE_COLOR
WriteRow wsSum, outRow, c, "Hardcoded value", "", refCell.Formula, c.Value
outRow = outRow + 1: nHard = nHard + 1
Else
' compare formula pattern (R1C1 is fill-down/right stable)
If Normalize(c.FormulaR1C1) <> refPat Then
c.Interior.Color = HILITE_COLOR
WriteRow wsSum, outRow, c, "Different formula", c.Formula, refCell.Formula, c.Value
outRow = outRow + 1: nDiff = nDiff + 1
End If
End If
NextCell:
Next c
Application.ScreenUpdating = True
With wsSum
.Range("D2").Value = "Hardcoded:": .Range("E2").Value = nHard
.Range("D3").Value = "Different:": .Range("E3").Value = nDiff
With .UsedRange
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
End With
' freeze header
.Activate
ActiveWindow.SplitRow = 5
ActiveWindow.FreezePanes = True
End With
MsgBox "Check complete." & vbCrLf & "Hardcoded: " & nHard & vbCrLf & "Different: " & nDiff, vbInformation
End Sub
' ===== helpers =====
Private Function Normalize(ByVal s As String) As String
' normalize formula text for comparison (case/space-insensitive)
Normalize = UCase$(Replace(Trim$(s), " ", ""))
End Function
Private Sub WriteRow(ByVal ws As Worksheet, ByVal outRow As Long, _
ByVal tgt As Range, ByVal status As String, _
ByVal curA1 As String, ByVal refA1 As String, _
ByVal curVal As Variant)
ws.Cells(outRow, 1).Value = tgt.Address(False, False)
ws.Cells(outRow, 2).Value = status
ws.Cells(outRow, 3).Value = Left$(curA1, 180)
ws.Cells(outRow, 4).Value = Left$(refA1, 180)
ws.Cells(outRow, 5).Value = Left$(CStr(curVal), 180)
End Sub
Private Function ColLetter(ByVal colNum As Long) As String
Dim s As String: s = ""
Do While colNum > 0
s = Chr$(((colNum - 1) Mod 26) + 65) & s
colNum = (colNum - 1) \ 26
Loop
ColLetter = s
End Function
File #3: Magic Cleaning (Python)
3 Magic Cleaning.xlsxFile #4: Forecasting, Visuals, Outliers, Oh My! (Python)
4 Forecasting Stable.xlsxPMI AI EXECUTIVE DASHBOARD 4.79 stable no macro.xlsxDEMO 15 Files Solved, Finally!
Document mapping for PC
A) Create a test folder structure (or skip this step and use your own)
Find a folder where we will add some test folders
Right click the folder and choose "copy as path"
Open Powershell (Windows Search: Powershell)
Type: $root =
Ctrl + V to enter your path
Hit Enter
Paste the following into powershell, say paste anyway hit enter, and say continue:
$projects = "ProjectA","ProjectB","ProjectC","ProjectD","ProjectE","ProjectF","ProjectG","ProjectH","ProjectI"
foreach ($proj in $projects) {
$projPath = Join-Path $root $proj
# Documentation/Specs
$specsPath = Join-Path $projPath "Documentation\Specs"
New-Item -Path $specsPath -ItemType Directory -Force | Out-Null
New-Item -Path (Join-Path $specsPath "Requirements.txt") -ItemType File -Force | Out-Null
# Documentation/UserManual (only for some projects)
if ($proj -in @("ProjectA","ProjectC","ProjectE","ProjectG","ProjectI")) {
$manualPath = Join-Path $projPath "Documentation\UserManual"
New-Item -Path $manualPath -ItemType Directory -Force | Out-Null
New-Item -Path (Join-Path $manualPath "UserGuide.txt") -ItemType File -Force | Out-Null
}
# SourceCode/Module1
$mod1Path = Join-Path $projPath "SourceCode\Module1"
New-Item -Path $mod1Path -ItemType Directory -Force | Out-Null
# SourceCode/Module2
$mod2Path = Join-Path $projPath "SourceCode\Module2"
New-Item -Path $mod2Path -ItemType Directory -Force | Out-Null
New-Item -Path (Join-Path $mod2Path "main.txt") -ItemType File -Force | Out-Null
# Tests/UnitTests
$testsPath = Join-Path $projPath "Tests\UnitTests"
New-Item -Path $testsPath -ItemType Directory -Force | Out-Null
New-Item -Path (Join-Path $testsPath "test_main.txt") -ItemType File -Force | Out-Null
}
Write-Host "Folder structure created successfully under $root"
Find a folder you want to analyze, or the test folder.
Right click the folder and choose "copy as path"
Open Powershell (Windows Search: Powershell)
Type: $Path =
Then paste the path
Press enter
Now type: Tree $Path /F
Now we want to save this to a notepad file so type: tree $Path /F > (Join-Path $Path "MyTree.txt")
Flag duplicates immediately so type:
Get-ChildItem -Path $Path -Recurse -File |
Group-Object -Property Name |
Where-Object { $_.Count -gt 1 } |
Select-Object Name, Count, @{Name="Locations";Expression={($_.Group | Select-Object -Expand FullName) -join "; "}}
Now save it to an Excel file so type:
Get-ChildItem -Path $Path -Recurse |
Select-Object FullName, DirectoryName, Name, Extension, Length |
Export-Csv -Path (Join-Path $Path "TreeView.csv") -NoTypeInformation
Press enter
Paste the path
Page updated
Google Sites
Report abuse