Sat 17 Mar 2018

Solution - Challenge 70 - Find Duplicates and Show the Count

By |Saturday, March 17th, 2018|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 70 - Find Duplicates and Show the Count

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF($B$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from Solution - Challenge 70 - Find Duplicates and Show the Count

Thu 09 Nov 2017

Challenge 70 - Find Duplicates and Show the Count

By |Thursday, November 09th, 2017|Categories: Challenges|Tags: , , |2 Comments

You have been given following data and you need to show corresponding count. You need to write a formula which can generate this count. If needed, you can use a maximum of helper column also.

Download problem workbook from Challenge 70 - Find Duplicates and Show the Count

The solution to this problem will be published after a month i.e. on 17-Mar-18.

Sat 24 Jun 2017

Excel Quiz 53

By |Saturday, June 24th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , , , , , , , , , |0 Comments

Excel Quiz 53 - A Quiz on Data Tab in Excel

This quiz checks your knowledge about Data tab in Excel.

Sun 13 Mar 2016

Article 37 - VBA - Generating Unique (Non-repeating) Random Numbers Efficiently

By |Sunday, March 13th, 2016|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100.

The algorithm to ensure uniqueness is following -

1. Given lower limit and upper limit, generate all numbers and populate an array. Now, this array contains all numbers between lower limit and upper limit sequentially. Hence, in case of 10 to 100, it will contain entries 10, 11, 12, .....99,100.
2. Next is to shuffle the array randomly using Fisher Yates algorithm so that the array contains the numbers 10, 11, 12...99,100 in a random order.
(more…)

Sat 11 Apr 2015

Article 13 - Generate a Unique / Distinct List out of a List when no blanks in the list / range

By |Saturday, April 11th, 2015|Categories: Articles|Tags: , , , |0 Comments

Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns.

Note - This article is for the list which has no blanks in the range select / list. The case of having blanks in data range will be investigated into another article.

1

We will revert to tackle this problem in 6 ways

A. Manual Way
B. Hybrid Way
C. Formula Way
D. MS Query Way
E. Power Pivot Way
F. VBA Way

(more…)

Sat 02 Aug 2014

Tips & Tricks 36 - Highlight Unique / Duplicate Rows

By |Saturday, August 02nd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you want to highlight Unique / Duplicate Rows, follow following steps

1. Select the data range where you want to apply this formatting.

2. Home Tab > Conditional Formatting > New Rule

1

3. Select Format only unique or duplicate values. In Format all: box, you can select duplicate / unique.

4. Format appropriately and press OK.

Sat 21 Jun 2014

Tips & Tricks 18 - Count No. of Unique Values

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , |0 Comments

Use following formula to count no. of unique values -

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Credit - There are various ways to accomplish above. I knew all other formulas. But the above formula is the sleekest. I learnt it from a posting to Excel Microsoft Community.

Sat 17 May 2014

Tips & Tricks 1 - A List is Unique or Not (Whether it has duplicates)

By |Saturday, May 17th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Assuming, your list is in A1 to A1000. Use following formula to know if list is unique.

=MAX(FREQUENCY(A1:A1000,A1:A1000))

=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))

If answer is 1, then it is Unique. If answer is more than 1, it is not unique.