Solved

Excel compare and combine data from two sheets

Posted on 2014-03-26
5
524 Views
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.
TEST.xlsx
0
Comment
Question by:IT Tech
  • 3
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
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.  

=IFERROR(VLOOKUP($F2,Sheet1!$E$2:$DQ$315,117,0),"")

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.
0
 

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.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 39957695
Sure thing.  Glad I could help.

=IFERROR(VLOOKUP($F2,Sheet1!$E$2:$DQ$315,117,0),"")

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.
0
 

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.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 39960278
np
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the different options available in the Backstage view in Excel 2013.

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now