background preloader


Facebook Twitter

Thank You for Downloading How To Use Excel. Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

Thank You for Downloading How To Use Excel

Yes, you could always open the two Excel documents and copy and paste cell by cell. But what happens when your spreadsheet contains hundreds of rows? VLOOKUP is your answer. In short, this function uses a unique identifier -- like an email address or SKU number -- to match data from two different sources. For example, you might have some data from HubSpot and some from Salesforce that you want to combine together. Before you use the formula, copy and paste your data so they are in two different sheets in the same Excel document. Then, you’ll have to be absolutely sure that you have at least one column that appears identically in both Sheet 1 and Sheet 2. Here’s what the formula looks like: =VLOOKUP(lookup value, table array, column number, [range lookup]) With values included, it becomes something like this: =VLOOKUP(C2,Sheet2! There’s no need to memorize this formula. Basic tasks in Excel 2013 - Excel. Excel is an incredibly powerful tool for getting meaning out of vast amounts of data.

Basic tasks in Excel 2013 - Excel

But it also works really well for simple calculations and tracking almost any kind of information. The key for unlocking all that potential is the grid of cells. Cells can contain numbers, text or formulas. You put data in your cells and group them in rows and columns. That allows you to add up your data, sort and filter it, put it in tables, and build great-looking charts. 22-May-2009 NEW UTILITY: Merge 2 Data Sets. This ulility can save you an enormous amount of time if you have a task involving merging 2 data sets.

22-May-2009 NEW UTILITY: Merge 2 Data Sets

If you work in Data or Operations, this could be the file for you ! You list the 2 sources of the dataThis can be Excel or text files.You then list where the data is on those data sourcesThe utility will then open both, merge the data, and save an output file. Here's the main control panel: So in our example, we merge this: With this: To produce this: Note: An important thing to enter is "Calculate Last Row From Column" This column should be the one that contains the most data.If it isn't, your data might not be aligned in the output file. Note Also: The utility works with the columns in any orderIt assumes that Data Source 1 contains the headings you want to use in your output file.There is no error checking, so this utility will break if you do something unexpected.Works with Excel 2007... To replicate: Spreadsheet Utilities. How To Send Emails From An Excel Spreadsheet Using VBA Scripts. Using emails as part of any program is a nice way to automate important tasks, and it also significantly improves the value and functionality of any program or script.

How To Send Emails From An Excel Spreadsheet Using VBA Scripts

In the past, I’ve used email a whole lot in my batch jobs and other automated scripts, just like I’ve described in past articles here on using tools like Sendmail or Blat to issue emails straight from the command line, or from within a command line script. These are great for those times when you have a script that’s monitoring the health of a computer or the status of a specific process, but what if you want to automate sending emails from within Office products like Word or Excel? There are a lot of reasons why you might want to do so. Maybe you have staff that update documents or spreadsheets on a weekly basis, and you’d like to receive an email notification of when those updates take place, and even a report of the data from within those sheets.

Now you’re ready to use CDO to issue emails from inside Excel. Shutterstock. Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet. Its integration with Windows allows control of Internet Explorer in a number of surprising ways using Visual Basic for Applications (VBA) script from any application that supports it, such as Word, Outlook or Excel.

Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet

VBA automation – especially directly automating a browser like IE as you’ll see in this article – is exactly the sort of thing that elevates VBA from a convenient programming script into a powerful automation language. What makes it so awesome is the fact that many applications with controls or objects are created simply for the purpose of allowing you to integrate into it using the VBA programming language. Through the years, we’ve showed you how to do some really cool stuff with VBA. Excel VBA Tutorial - Easy Excel Macros. VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs. 1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros.

Excel VBA Tutorial - Easy Excel Macros

In this chapter, learn how to create a simple macro. 2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. 3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA. 4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. Excel VBA Tutorial - Easy Excel Macros.