Solved

Excel compare and combine data from two sheets

Posted on 2014-03-26
5
523 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.

746 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

14 Experts available now in Live!

Get 1:1 Help Now