About eforexcel

This author has not yet filled in any details.
So far eforexcel has created 439 blog entries.
Sat 09 Dec 2017

Solution - Challenge 67 - Find Duplicates and Show the Count

By |Saturday, December 09th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 67 - 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 - Find Duplicates and Show the Count

Sat 09 Dec 2017

Tips & Tricks 167 - Shortcut for Refresh All Pivot Tables Not Working

By |Saturday, December 09th, 2017|Categories: Tips and Tricks|Tags: , , , , |0 Comments

To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task.

But on most of the computer this shortcut may not work. The reason is most of the CTRL+ALT+Fn shorcut keys are reserved by Intel HD Graphics card. You will need to de-reserve this key combination.

Control Panel > Intel HD Graphics (or anything related to Graphic Card, you can put Graphics in Search of Control Panel) > Options and Support > Disable the hot keys even though CTRL+ALT+F5 is not listed there.

1

Sat 02 Dec 2017

Excel Quiz 57

By |Saturday, December 02nd, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 57

A general quiz on Excel

Sat 25 Nov 2017

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA|Tags: , , , , , |0 Comments

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

(more…)

Sat 18 Nov 2017

Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday

=TEXT(A1&"Jan2017","dddd")

To show only 3 characters of the Weekday Name

=TEXT(A1&"Jan2017","ddd")

You can add a number to A1 if you want to show some other Weekday Name

Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Tue 14 Nov 2017

Solution - Challenge 66 - Find the Position of Word

By |Tuesday, November 14th, 2017|Categories: Solutions|Tags: , |0 Comments

Below is a proposed solution for the Challenge 66 - Find the Position of Word

Put the following formula and drag down. Let's assume the word "and" in cell D2 -

=LEN(REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),""))-LEN(SUBSTITUTE(
REPLACE(A2,SEARCH(" "&$D$2," "&A2),LEN(A2),"")," ",""))+1

You may download the solution workbook - Solution - Challenge 66 - Find the Position of Word

Sat 11 Nov 2017

VBA - Function to Validate IP Addresss

By |Saturday, November 11th, 2017|Categories: VBA|0 Comments

Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255

You can put following type of construct in a cell

=IsValidIP(A2)

(more…)

Thu 09 Nov 2017

Challenge 67 - 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 Find Duplicates and Show the Count

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

Sat 04 Nov 2017

Tips & Tricks 165 - Convert Weekday Names to Numbers

By |Saturday, November 04th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Suppose Cell A2 contains weekday names like Sunday, Monday.....(or Sun, Mon...), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7.

=ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0)

=MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0)

If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1 and Sun = 7

=ROUND(SEARCH(LEFT(A2,2),"MoTuWeThFrSaSu")/2,0)

=MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)

Sat 28 Oct 2017

Excel Quiz 56

By |Saturday, October 28th, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 56

A general quiz to test your Excel awareness