All articles with 'duplicates' Tag
Find out if a number has repetitive digits [formula homework]
Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.
For example, if A1 has 123405, then answer should be FALSE
and if A1 has 123455, then answer should be TRUE
Go ahead and post your answers (formulas, VBA or M script) in the comments section.Continue »
Check if 2 ranges have same values (set equality problem)
Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?Continue »
Compare 2 sets of data by letter or word & highlight mismatches [vba]
We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:
[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?
Its late 2014 and I am not done comparing.
So today, let’s talk about an interesting comparison scenario.
Comparing by letter or word
See above demo to understand the concept. Read more to learn how to do this.Continue »
Distinct Count & Blanks – Power Pivot Real Life Example
When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”
And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.
- We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
- We can use VBA, but it would become slow as you add more data.
- We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
- We might as well shave our head with a shovel before manually counting values.
And that brings us to 2 distinctly simple solutions:
- Using Power Pivot & Excel 2010
- Using regular pivot tables in Excel 2013
Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.Continue »
How many values are common in 2 lists? [homework]
Here is a formula challenge for you. Lets say we have 2 lists of values in A1:A10 & B1:B10 Now, how do you find the number of common values in both lists? We just want the count, not list of common values it self. Go ahead and figure out the formula and post your answers […]Continue »
Check if a list has duplicate numbers [Quick tip]
A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.
Today, lets learn a simpler formula to check if a list has duplicate numbers.
Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.Continue »
Even Faster Way to Compare 2 Lists in Excel [Quick Tip]
Last week we discussed a fun and easy way to compare two lists of data in excel using conditional formatting. In that post, Artem commented, The quickest way to find all about two lists is to select them both and them click on Conditional Formatting -> Highlight cells rules -> Duplicate Values (Excel 2007). The […]Continue »
Twins, Clones and Duplicates – 6 Excel Shortcuts from an Excited Dad
I could barely sleep last night. Even though I came home, my heart was in that hospital room only. I got up at 5:30, and came to see my wife and kids by 7:30. Now that they all are sleeping, I can quickly whip an excel post, to help you make twins, clones and duplicates.Continue »
Count the number of unique values in a range [Quick Tip]
Here is an excel formula quick tip that can come handy when you need to count the number of unique values in a range of cells. Assuming we have a list of values in the range:B5:B15 and we want to know how many unique values are there,
you can use the almighty SUMPRODUCT formula like this:
Read the rest of this post to understand how the formula works. You can also find resources to work with duplicate values in excel.Continue »
Using Array Formulas in Excel – Find if a list has duplicate items
In this installment of our spreadcheats series, we will learn how to use array formulas and findout if a list has any duplicate items. We will use Countif and array formulas to do this …Continue »
One more method to find unique values in excel and you can call me a dork
Use Excel Pivot tables to find and extract unique items in your data. This method is very fast and easily scalable.Continue »
Excel 2007 Review – 10 things that WOWed me
After a really long wait finally I have used … Excel 2007 (drum roll) and contrary to what many people think, I have found Excel 2007 to be a very well designed piece of software. Of course there are various issues with it and I am sure folks at MS are working on them so that next versions of MS Office are much more pleasant and simpler to use.
I wanted to share 10 wow factors in Excel 2007 that may convince you to try it.Continue »
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]
Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least. So […]Continue »
Excel Links of the Week [Nov 3]
The World’s First Music Video in Excel ACDC Rock n Roll Train This is totally cool. ACDC released an ASCII version of their “rock n roll train” video through excel spreadsheet. Why? So that you can download it at your office and watch it while your boss thinks you are working. 😀 I think this […]Continue »