Clean up and summarize data
How to count how many distinct values appear more than once
Count repeated codes once each, rather than counting duplicate rows. Summarize by code, filter counts above one, then count the remaining codes.
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.
Clean up and summarize data
Why does joining two Excel tables create extra rows?
Two orders and three addresses for one customer produce six matching pairs. Check repeated join keys before deleting rows that may contain valid information.
Clean up and summarize data
How to filter categories by their total, not individual amounts
Summarize amounts before applying a total threshold. Keep accounts totaling at least 100000 even when every individual transaction is smaller.
Find missing or changed values
How to find missing values between two Excel lists
Compare two lists without sorting them first. Find product codes in both sheets or in only one, then check whether a missing match needs action.
Clean up and summarize data
How to calculate minimum, maximum and average by group
Put a team's minimum, maximum and average processing times in one summary. Understand why blank records do not count as zero in the average.
Join data
How to match Excel data using two conditions without a lookup formula
Match both product and branch to bring back the correct price. Use two join keys and check duplicate combinations before calculating totals.