Go Premium for a chance to win a PS4. Enter to Win


Excel compare and combine data from two sheets

Posted on 2014-03-26
Medium Priority
Last Modified: 2014-03-27
Hello there,

I have an ongoing project in Excel.  I take a monthly report and combine it into a new column on a sheet with previous months data.  Only problem is the new report has fewer entries than that of the previous months and in order to combine them properly and make sure data is intact in the proper rows and column, I have to manually go through and compare the new sheet (sheet1) with the existing sheet (Monthly) and move each enter from column DQ (ISO Profit) on sheet1 into a new column in the Monthly sheet into the proper rows.  For this months report if I copy column DQ over to the Monthly sheet it is 49 rows less data than the previous months.  I am attaching the workbook for you.  So I was wondering if there was a way to automate this moving the data from column DQ in Sheet1 and put the data into the proper matching row in a new column in the Monthly sheet.  Thank you in advance for your taking a look at this.
Question by:IT Tech
  • 3
  • 2

Accepted Solution

rfportilla earned 2000 total points
ID: 39957096
Try setting this in Monthly!DY2 and copying it down.  There will likely have to be some modifications each month, but I think this will help speed it along.  


Open in new window

If you don't want to leave the function embedded, then just use it to fill the data and then do a copy / paste special of values over the column.

Let me know if you need me to break down the function in detail.

Author Closing Comment

by:IT Tech
ID: 39957221
This works like a top! Thank you very much.  It is going to save me hours. As for the breakdown of the function, it would be interesting for me to know how it works and maybe in the future I can learn some more on it. Thank you again.

Expert Comment

ID: 39957695
Sure thing.  Glad I could help.


I will start with the inner function, VLOOKUP.  This function looks up a value in a list and can look across that row and return another value.  (similar to looking up a phone number in a phone book)

It has 4 parts.  
1. "$F2" refers to where the lookup value is.  $ is an anchor.  The F column is always going to be the column in which the lookup field will exist.  The 2, the row number, is going to change as you copy this down.  
2. Sheet1!$E$2:$DQ$315 is the range in which to look.  If you have any trouble understanding the cell referencing, there are lots of places to look this syntax.  I only went to row 315 because that is all you had in the data source.  If next month has 400 records, you will need to change the 315 to 400.  
3. 117 is the column with the data you want returned.   From E2 to DQ, there are 117 columns.  The first column (in this case E2) is always the lookup field.  You have a lot of hidden columns, so the lookup range ended up being very wide.  Therefore, the 117th column had what you actually want to pull in.  I don't imagine this should change very much, but if it does, you will know how to determine it.  
4. 0 : This last part is an idiotic addition to this function.  Microsoft decided that the default behavior of vlookup should be that if it doesn't find an exact match, it should just give you what it thinks is the closest.  Setting this to 0 or false forces it to return exact matches only.

The outer function is iferror. This function captures errors and returns something else in it's place.  I am using to throw away any errors that vlookup gives me, such as it couldn't find a match.  

This has 2 parts.
1. This is the function that you want to run and capture errors on.  In this case, it is the vlookup function
2. "" is just an empty return.  If I get an error, then I just want to return nothing because it could not find a match.

There is plenty of documentation on both of these functions in Excel and on the Internet.

Hope this helps.  Good luck.

Author Comment

by:IT Tech
ID: 39960197
Hello Rfportilla,

Thank you very much for the informative walk through.  I appreciate it very much and thank you for the time and effort you put forth in this.

Expert Comment

ID: 39960278

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn the basics of formula auditing in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question