Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Excel compare and combine data from two sheets

Posted on 2014-03-26
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 500 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Excel: if cell = x, add 4 50
excel 2013 substitute function 8 93
Finding a closest match in Excel 7 67
what is the best open source database solution? 3 39
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

792 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