In a previous post I created unqiue distinct column headers (A20:C20) and then categorized adjacent cell values into each column (vba).
In this post I´ll show you how to do the same using only excel formulas.
Create unique distinct column headers
Array formula in B20:
=INDEX($B$4:$B$13, MATCH(0, COUNTIF($A$20:A20, $B$4:$B$13), 0)) + CTRL + SHIFT + ENTER.
Copy (CTRL + C) [...]

I am fairly new to vba and I am amazed of how much you can automate in excel.
In this post I am going to categorize values from a list into unique columns.I am sure there are more efficent ways to accomplish this task using better written code. In  a year from now  I hope I´ll [...]

Count unique distinct numbers in a 3D range
Array formula in E8:
=SUM(–(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)<>0)) + CTRL + SHIFT + ENTER
Count unique numbers in a 3D range
Array formula in E10:
=SUM(–(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)=1)) + CTRL + SHIFT + ENTER
Count duplicate numbers in a 3D range
Array formula in E12:
=SUM(–(FREQUENCY(Sheet1:Sheet3!$B$2:$D$4, Sheet1:Sheet3!$B$2:$D$4)>1)) + CTRL + SHIFT + ENTER
Download excel tutorial file
[...]

By coincidence I seem to have created a “unique” formula in this post: Combine data from multiple sheets in excel (See comments). I have no clue if that is true or not but my intention now is to develop that formula a bit further.
Values across multiple sheets are sometimes refered to as a 3D [...]

In this post I am going to show how to create a new sheet for each airplane using vba. The macro copies airplane and model values into each new sheet.
Before:

After:

The Code
Option Explicit
Sub Splitdatatosheets()
‘
‘ Splitdatatosheets Macro
‘
‘
Dim rng As Range
Dim rng1 As Range
Dim vrb As Boolean
Dim sht As Worksheet
Set rng = Sheets(“Sheet1″).Range(“A4″)
Set rng1 = Sheets(“Sheet1″).Range(“A4:D4″)
vrb = False
Do [...]

Question:
Problem description (simplified of course):
I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each TAB is a separate month (first is Jan, 2nd is Feb, etc.). [...]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both vba and formula.
I will explain how I created this calendar in an upcoming post. You can download the excel calendar file here: Excel calendar.xlsm You need to enable macros to use this calendar.

Instructions:
Select a week

Select [...]

Table of contents

Introduction
Create a lookup table
Create a drop down list
Change web query parameter
Add dynamic ranges to stock chart

Introduction
I have previously posted how to create  a dynamic stock chart in excel. It is really easy to use:

Select Company
Click “Update chart”

In this post I have added a new feature. You can now select a date range in [...]

Question: How to create unique distinct year and months from a long date listing (column A)?
You can find the question in this post: Extract dates using a drop down list in excel
Answer:

Array formula in C2:
=TEXT(INDEX($A$2:$A$135, MATCH(0, COUNTIF($C$1:C1, TEXT($A$2:$A$135, “mmm-yyyy”)), 0)), “mmm-yyyy”) + CTRL + SHIFT + ENTER copied down as far as needed.
Download [...]

In this blog post I´ll show you how to update a stock chart in excel using a drop down menu and a web query connection.

Create list of companies and corresponding tickers
Create a dynamic range
Create a drop down list
Calculate dates
Create a web query
Create a macro to refresh web query and automate “text to columns”
Create a stock [...]