Excel compare and combine data from two sheets

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.
IT TechAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

rfportillaConnect With a Mentor Commented:
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.
IT TechAuthor Commented:
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.
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.
IT TechAuthor Commented:
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.
All Courses

From novice to tech pro — start learning today.