Sat 04 Apr 2015

Challenge 11 - Generate a Repeating Number Sequence - II

By |Saturday, April 04th, 2015|Categories: Challenges|Tags: , , |1 Comment

Last time, I posed a challenge to generate a repeating number sequence. This time, there is small twist. The sequence which you need to generate is 3 times each number i.e. 111222333444555...........

The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in C8, dragging down should produce the below series.1
1
1
2
2
2
3
3
3
4
4
4

Note - If you wish, you may like to post your answer in the comments.

Sat 28 Mar 2015

Challenge 10 - Generate a Repeating Number Sequence - I

By |Saturday, March 28th, 2015|Categories: Challenges|Tags: , , |1 Comment

This time challenge is to write a formula which can be dragged down to generate number sequence 1,2,3,4,5 and repeats this sequence again. The formula should be flexible enough to be put in any cell and be dragged down. Hence, if I put the formula in D4, dragging down should produce the below series.

1
2
3
4
5
1
2
3
4
5
1
2
3
4
5

Note - If you wish, you may like to post your answer in the comments.

Sat 08 Nov 2014

Article 7 - Generate a Sequence of Numbers

By |Saturday, November 08th, 2014|Categories: Articles|Tags: , , , , , |1 Comment

Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.

Once again, I would like to tackle this topic in 4 ways.

1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA

(more…)

Sat 27 Sep 2014

Tips & Tricks 55 - Quickly Fill in Cells with Dates from M to N

By |Saturday, September 27th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example.

2. Select that Cell and in Home Tab, go to Fill and Select Series

1

3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop value as we want to generate dates till that end date and OK to generate list of numbers. Step value consists of increments by which dates have to be incremented. (If you want to space out dates with one week interval, put 7 in step value)

1

Sat 05 Jul 2014

Tips & Tricks 30 - Generate an Array of a to z

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

I have already talked about generating a to z in a sequence in below post.

Now, I want to generate an array {"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v",
"w","x","y","z"} to use in a formula.

To generate Small or Capital letters, do it while generating the sequence itself.

Let's assume that a to z are generated in A1 to A26. Put a in A1 and drag down till A26. If you want to generate a to z in capital letter, put A in A1 and drag down.

In a cell, put following formula and press F9

= TRANSPOSE(A1:A26)

In the formula bar, you will have array generated which you can copy and paste and use in the formula.

1

Sat 05 Jul 2014

Tips & Tricks 29 - Generate Alphabets A to Z (or a to z)

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

For Capital A to Z

Put A in first cell and drag down.

If you want to do it through formula -

In Cell A1, put this formula and drag it till 26th row

=CHAR(65+ROW()-1) or CHAR(64+ROW())

CHAR(65) is A. You can verify this by CODE("A") formula which will generate 65.

If you are putting this formula is some other row apart from 1, adjust the mathematical expression within CHAR in such a manner that first entry is 65.

For example, if you are starting in row 10, the formula would become

= CHAR(65+ROW()-10) or CHAR(64+ROW()-9)

For Small a to z

Put a in first cell and drag down.

If you want to do it through formula -

Replace 65 with 97 or 64 with 96 in above formulas.