Excel
Concatenate Text With a Separator Both Ways in Excel
Join text with a separator in Excel using TEXTJOIN and the ampersand and in SQL with concat_ws and string_agg, with blanks, NULLs and order handled.
7 min read
Topic
Data Analysis
Answers from raw rows: aggregates, cohorts, funnels, time series, plus the string and text wrangling that precedes every report.
Excel
Lookup With Approximate Match Versus SQL Range Join
Compare Excel approximate match lookups with SQL range joins for tiers, rates and bands: how each handles gaps, boundaries and overlaps, and which to use.
7 min read
Excel
Count Distinct Customers per Month Both Ways in Excel
Count distinct customers per month in Excel with UNIQUE and FILTER, SUMPRODUCT or a PivotTable, and in SQL with COUNT(DISTINCT), with blanks handled.
6 min read
Excel
Remove Duplicates Keeping the Latest Row Both Ways
Remove duplicate rows but keep the latest per key, in Excel with sort and Remove Duplicates, XLOOKUP or SORTBY, and in SQL with DISTINCT ON or ROW_NUMBER.
7 min read
Excel
Rank Customers by Spend in Excel and SQL With Ties Handled
Rank customers by spend with RANK.EQ and sorted lists in Excel and with RANK, DENSE_RANK and ROW_NUMBER in SQL, including ties, top N and ranks per group.
7 min read
Excel
Running Total of Monthly Sales Both Ways in Excel and SQL
Build a monthly running total in Excel with expanding ranges, SUMIFS and SCAN, and in SQL with a window function, including ties and resets.
7 min read
Excel
Joining Two Sheets on Two Keys in Excel and in SQL
Join two sheets on two keys in Excel with XLOOKUP, SUMIFS or Power Query, and in SQL with a two-column join, handling duplicates, NULLs and missing matches.
6 min read
Excel
Finding the Second Highest Value in Excel and in SQL
Find the second highest value in Excel with LARGE and MAXIFS and in SQL with OFFSET, DENSE_RANK and subqueries, and decide how ties and NULLs should count.
6 min read
ExcelGuide
Worked Examples Moving Between Excel and SQL Side by Side
Worked examples of one task in Excel and SQL: ranking, two-key joins, running totals, deduplication, distinct counts and percentiles, with the differences.
17 min read
XML
Parsing Tax Style E Invoices and Checking the Totals
Read a UBL style invoice with namespaced lines, recompute each line, sum the net amounts and compare the tax rounded per line with the tax per rate group.
6 min read
XML
Parsing a Bank Statement in XML and Reconciling the Balance
Read an ISO 20022 style account statement, apply the credit and debit sign, and check that opening balance plus entries equals the closing balance.
7 min read
YAML
Finding Duplicate Keys Across a Config Repository Reliably
Find YAML keys repeated inside a file and keys that differ across environment files, with ruamel.yaml, yamllint and a flatten pass over a repository.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.