Excel Lookup Function lookup multiple criteria

Posted on February 21, 2015September 16, 2017 by Wyman W

This Excel tutorial explains how to use Excel Lookup Function and how to look up multiple criteria. You may also want to read: Excel Vlookup second matched value or specific occurence Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel Lookup Function - lookup multiple criteria Excel Lookup Function is to look up a value in a Range (or array) and return the corresponding result in another Range (or arra...

Excel Lookup Function lookup multiple criteria not in first column

Posted on February 19, 2015February 19, 2015 by Wyman W

Excel Lookup Function lookup multiple criteria not in first column In this tutorial, I will explain how to use Excel Lookup Function to lookup multiple criteria while the lookup value is not in first column of lookup range. The reason for writing this article is that Vlookup can only lookup one criteria and lookup value must be in the first column of lookup table. You can also use SUMPRODUCT Function to achieve the same result in the below article. Excel SumProduct lookup multiple crite...

Excel SumProduct lookup multiple criteria

Posted on February 19, 2015March 20, 2015 by Wyman W

Excel SumProduct lookup multiple criteria not in first column In this tutorial, I will explain how to use Excel SumProduct Function to lookup multiple criteria while the lookup value is not in first column of lookup range. The reason for writing this article is that Vlookup can only lookup one criteria and lookup value must be in the first column of lookup table. You can also use Lookup Function to achieve the same result. Read the below article for details. Excel Lookup Function lookup...

Excel Function add year month day hour minute second to date

Posted on February 15, 2015February 27, 2015 by Wyman W

Excel Function add year month day hour minute second to date In this tutorial, I will show how to use Excel Function to: - add year to date - add month to date - add day to date - add hour to date - add minute to date - add second to date I recommend you to read my previous tutorial to understand the concept of time serial. In the following examples, I will assume A1 contains a date 2015 Jul 1. Add year to date Change the Cell Format to dd/mm/yyyy Assume that we add...

Access Excel VBA Check Prime Number using custom Function

Posted on February 14, 2015March 8, 2015 by Wyman W

This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE. Access Excel VBA Check Prime Number using custom Function In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number. 1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 ...

Excel VBA refresh all Pivot Table or Pivot Cache

Posted on February 14, 2015July 19, 2016 by Wyman W

This tutorial explains how to use Excel VBA to refresh all Pivot Table or Pivot Cache, and difference between PivotCache.Refresh and PivotTable.RefreshTable You may also want to read: Excel VBA refresh closed workbook Excel automatically refresh pivot table Excel VBA refresh all Pivot Table or Pivot Cache Understanding difference between Pivot Table and Pivot Cache To start with this topic, you should note the relationship and difference between refresh Pivot Table and Pivot Cache. ...

Use VBA Excel Function to sort data in ascending order

Posted on February 11, 2015April 20, 2015 by Wyman W

This Excel tutorial shows how to use custom VBA Excel Function to sort data in ascending order instead of using Filter. You may also want to read Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Use VBA Excel Function to sort data in ascending order In this tutorial, I will show how to use VBA Excel Function to sort data instead of using Filter. This was origin...

VBA Access Excel Remove duplicates in text

Posted on February 10, 2015April 7, 2015 by Wyman W

This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter You may also want to read: Access Excel Scripting Dictionary store unique array item VBA Access Excel remove duplicates in text This tutorial is to help you to remove duplicates in text (in a Cell), but it is not about removing duplicated Cell value in a column. To remove duplicated Cell value in a column, highlight the column, navigate ...

Excel VBA Consolidate worksheets into one worksheet

Posted on February 10, 2015May 12, 2016 by Wyman W

This Excel tutorial explains how to combine / consolidate worksheets into one worksheet. You may also want to read: Excel VBA combine worksheets columns into one worksheet Excel VBA consolidate multiple workbooks into one workbook Excel VBA Consolidate worksheets into one worksheet In this tutorial, I will show how to consolidate worksheets into one worksheet within the same workbook using VBA. Data from different worksheets will paste to the last row (same column) of the consolidatin...

VBA Excel combine columns depending on header name

Posted on February 9, 2015February 9, 2015 by Wyman W

Excel Combine columns depending on header name using VBA I have seen many times that people ask questions related to this topic, below are some examples 1) To copy specific columns and paste to another workbook 2) To distribute reports from a master table 2) To group specific columns to apply the same formatting In this tutorial, I will explain how to combine columns using different methods. Example 1 - when column name or number is unknown Scenario: In a worksheet called "maste...