Sat 28 May 2016

## Excel Quiz 39

By |Saturday, May 28th, 2016|Categories: Quizzes||0 Comments

## Excel Quiz 39

2nd quiz on Excel short cuts

Sat 28 May 2016

## Tips & Tricks 137 - VBA - Delete Blank Rows for a Range Quickly (without Looping)

By |Saturday, May 28th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , |0 Comments

Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks.

You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same

```On Error Resume Next
Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0```

Mon 23 May 2016

## Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,
A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist

Sat 21 May 2016

## Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

By |Saturday, May 21st, 2016|Categories: Challenges||0 Comments

This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday.

1. Your sheets are named Day1, Day2.....Day7.

2. One sheet can have a maximum of 20 entries.

3. The entries may not be same everyday depending upon whether a new project has started or a project has finished.

4. You need to get all the consolidation in "Consolidated Weekly Sheet".

One typical day's sheet -

You need to write formulas (or VBA) to populate row 3 onwards

A typical answer would look like -

The worksheet related to this challenge can be downloaded from Challenge 43 - Consolidate Daily Sheets into a Weekly Sheet

The answer to the above challenge will be published after a month i.e. on 21-Jun-16.

Sat 21 May 2016

## Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles||0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

Sat 14 May 2016

## Excel Quiz 38

By |Saturday, May 14th, 2016|Categories: Quizzes||0 Comments

## Excel Quiz No. 38

A general quiz on Excel

Sat 14 May 2016

## Downloads 03 - Template 03 - A Flexible Agile Burndown Chart

The template can be download from - Template 03 - Agile Burndown Chart Template

I decided to create a template for a Burndown Chart in Excel which is very highly used in Agile Project Management. Next download template will be about Burnup Chart which is also a very highly used chart in Agile Project Management.

In this template, I am not constraining look and feel to only one type of Burndown Chart rather I am giving 4 different look and feel. You can use which one do you want to use.

Sat 14 May 2016

## Tips & Tricks 136 - Quickly Copy a Formula or a Value in a Large Number of Cells

By |Saturday, May 14th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast.

1. Put formula in B1.
2. CTRL+C of B1
3. CTRL+G and put the range say B2:B10000 in Reference: box (or even B1:B10000 as copying B1 in B1 will not cause any problem)
4. Press Enter

Mon 09 May 2016

## Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

By |Monday, May 09th, 2016|Categories: Solutions, VBA||1 Comment

Below is a possible solution to the challenge - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher

1. Make a backup of your workbook
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given below
6. Save your file as .xlsm
7. Call your function as =EncryptCS(A1,\$B\$2)

```Function EncryptCS(PText, Shift) As String
Dim Ws As Worksheet
Dim i As Long
Dim CText As String
Dim PArr, CArr
Application.Volatile
PArr = Split(StrConv(PText, vbUnicode), vbNullChar)
ReDim CArr(UBound(PArr) - 1)
For i = LBound(PArr) To UBound(PArr) - 1
If Abs(77.5 - Asc(UCase(PArr(i)))) < 13 Then
If Asc(LCase(PArr(i))) + Shift > 122 Then
CArr(i) = Chr(Asc(PArr(i)) + Shift - 26)
Else
CArr(i) = Chr(Asc(PArr(i)) + Shift)
End If
Else
CArr(i) = PArr(i)
End If
Next i
EncryptCS = Join(CArr, "")
End Function```

A workbook containing the above solution can be downloaded from Solution - Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher.

Sat 07 May 2016

## Challenge 42 - Split a Sentence Into Words in Different Cells

By |Saturday, May 07th, 2016|Categories: Challenges|Tags: , , , , , , , , |0 Comments

This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words.

Remember, it has to be a single formula which should be put into B1 and dragged to the right.

See the examples below -

A workbook containing this challenge can be downloaded from Challenge 42 - Split a Sentence Into Words in Different Cells

The solution to above challenge will be published on 7-Jun-16.