Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on
Learn how to find which worksheet a max or min value occurs on using this neat formula
CP021: How to quickly compare 2 lists in Excel
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 21st session of Chandoo.org podcast, lets compare lists. Quickly
What is in this session?
Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.
Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.
In this podcast, you will learn,
- Why I sound like I am on a secret mission at a mafia hideout.
- 5 ways to compare 2 lists
- Manual method
- Conditional Formatting
- Row Differences
- LOOKUP formulas
- COUNTIF formulas
- Bonus tip: Removing duplicates
- Conclusions
Countif – Quick Tip
Learn how to Count Text, Blanks and Non-Blank cells with this Countif() Quick Tip
Can you find that pattern? [Homework]
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
Your Homework:
In a range we have some resource types & their billing rates.
In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.
See above diagram.
Formula Forensics No. 032 – Creating Dynamic Charts with Non-Contiguous Data
There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.
Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.
Formula Forensics No. 030 – Extracting a Sorted, Unique List, Grouped by Frequency of Occurrence
Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.
Formula Forensics 025. Count Unique Values in a Range
Need to count how many unique values occur in a range?
Learn how to do this and how the formula works right here.