Clean up and summarize data
Find companies listed with more than one phone number
Find company IDs that have different phone numbers, without counting repeated copies of the same number. Review missing numbers separately.
Make spreadsheet work simpler
Match customer lists, find missing values, and combine spreadsheets. See how to solve everyday Excel problems with worked examples in JOIN SHEET.
Join data
Find members present in all three lists
Use two matching-row joins on unique IDs to retain only members found in every one of three lists.
Join data
Find missing items when each account type has different requirements
Check each account against the items its type requires. Find missing assignments even for accounts with no records at all.
Join data
Find the nearest numeric reference and keep ties
Find the closest reference number above or below each target. Compare squared differences and keep both references when they are equally close.
Join data
Find pairs of overlapping numeric ranges
Find ranges that overlap, including a range contained inside another. Compare each pair once and leave out pairs that only touch at an endpoint.
Clean up and summarize data
How do you find customers whose first purchase was this month?
Find each customer's earliest purchase in the full history, then filter those dates to the reporting month. This separates first purchases from repeat purchases during the month.