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

Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 09 Jan 2016

Excel Quiz 29 – COUNT Quiz – Part 2

By |Saturday, January 09th, 2016|Categories: Quizzes|Tags: , , , , , |0 Comments

Excel Quiz 29 - COUNT Quiz - Part II

A quiz on various functions used to perform counting in Excel.

Sat 26 Dec 2015

Tips & Tricks 124 - VBA - Check if a Range is Blank When Range Contains Formulas returning Blanks

By |Saturday, December 26th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not.

WorksheetFunction.CountA(Range("A1:A100"))

But if your range contains even a single formula which is returning blank, the above will not return the range as blank even though it contains literal blanks.

To handle this, you can use below to check if your range is blank even though the range contains formulas returning blanks.

Range("A1:A100").Rows.Count = WorksheetFunction.CountBlank(Range("A1:A100"))

 

Sat 14 Nov 2015

Tips & Tricks 117 - VBA - How to Count a particular character in a String

By |Saturday, November 14th, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string -

=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be 5.

To count the same in a range of cells -

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

Now, how to do the same thing in VBA. In VBA, you can use following expression to count this -

UBound(Split(LCase(Range("A1")), "a"))

To count the same in a range of cells -

UBound(Split(LCase(Join(WorksheetFunction.Transpose(Range("A1:A10")))), "a"))

Mon 09 Nov 2015

Solution - Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters

By |Monday, November 09th, 2015|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 27 – Count the Number of Alphabets and Numerals and Other Characters.

The formula for counting number of alphabets

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(65:90)),"")))

The formula for counting number of numerals

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

The formula for counting other characters

=LEN(SUBSTITUTE(A1," ",""))-C1-C2

Where C and C2 are number of alphabets and numerals.

Sat 31 Oct 2015

Tips & Tricks 116 - Count the Number of Words in a Cell / Range

By |Saturday, October 31st, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you have been given the following and you need to count the number of words in a cell or in a range.

1

Formula for calculating number of words in a cell -

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Formula for calculating number of words in a range -

=SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))

1

Sat 10 Oct 2015

Challenge 27 - Count the Number of Alphabets and Numerals and Other Characters

By |Saturday, October 10th, 2015|Categories: Challenges|Tags: , , , |1 Comment

Suppose, you have been given a string in A1 = "234, Washington D. C. @ 45609"

The challenge before you is to write 2 formulas to count the number of

1. Alphabets
2. Numerals
3. Other Characters other than space

For example, in above string, number of alphabets = 12 and  number of numerals = 8 and Number of other Characters other than Space = 4.

Once again, all 3 need to be counted separately. Hence, you have to make 3 formulas - One for alphabets and one for numerals and one for other Characters other than Space.

We should be able to use the formula in Excel 2003 also, hence multiple SUBSTITUTES will not be working here.

You may choose to pose your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 09-Nov-15.

Sat 11 Jul 2015

Excel Quiz 16 - COUNT Quiz - Part I

By |Saturday, July 11th, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

Excel Quiz 16 - COUNT Quiz - Part I

Quiz on various COUNT functions provided by Excel. This is part I of II for COUNT Functions quizzes. This quiz covers only those Excel functions which start with the word COUNT.

Sat 11 Apr 2015

Excel Quiz 9

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

The Ninth quiz in the series to test your formula knowledge mostly in SUM and COUNT areas. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.