Archive for the ‘Excel’ Category

Categorize values into multiple columns (excel formulas)

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) [...]

Comments (2)

Categorize values into multiple columns using vba in excel

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 [...]

Leave a Comment

Count unique distinct numbers across multiple sheets (3D range) in excel

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
[...]

Leave a Comment

Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

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 [...]

Leave a Comment

Split data across multiple sheets in excel (vba)

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 [...]

Leave a Comment

Combine data from multiple sheets in excel

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.). [...]

Comments (3)

Calendar with scheduling in excel 2007 (vba)

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 [...]

Leave a Comment

Dynamic stock chart in excel – Add date ranges

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 [...]

Comments (2)

Create unique distinct year and months from a long date listing in excel

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 [...]

Leave a Comment

Create a dynamic stock chart using a web query and a drop down list in excel

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 [...]

Leave a Comment