Solved

Copying data from one Excel 2007 spreadsheet to another when destination spreadsheet may not contain all same rows

Posted on 2014-03-12
6
292 Views
Last Modified: 2014-03-12
I have two spreadsheets of school names.  One spreadsheet contains 11 columns without headers.  Column K contains data that needs to be copied to the other spreadsheet.

The destination spreadsheet also has 11 columns, but with headers.  The information from Column K of spreadsheet #1 (High-Schools-2014-with-PAAK) needs to be copied to spreadsheet #2 (high-schools-Destination-File).  However, the information in each cell in column K must match with the correct school name.  

Not every school in the destination file appears in the first file, therefore would not have data copied over to column K.  There may be schools in spreadsheet #1 that do not appear in the destination file.  

I need some type of formula that will look at the school name in Column A, copy the data in Column K, and paste into the proper cell in the destination spreadsheet corresponding to the same school name.  

Is this possible?
High-Schools-2014-with-PAAK-code.xlsx
high-schools-Destination-File.xlsx
0
Comment
Question by:gsfc
  • 4
6 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39924329

I need some type of formula that will look at the school name in Column A, copy the data in Column K, and paste into the proper cell in the destination spreadsheet corresponding to the same school name.  

You need this as formula ? Could VBA be an option ?
gowflow
0
 

Author Comment

by:gsfc
ID: 39924391
I'm not familiar with VBA.  If this is something you can provide to me, perhaps I can sk another in our department to assist me.
0
 
LVL 1

Accepted Solution

by:
Thymos68 earned 500 total points
ID: 39924433
The Vlookup formula would be a simple way to populate that data.  If you're unfamiliar with Vlookup, one could describe it as a means to use to pull the desired data into a cell, by way of a lookup table.  

Vlookup will look down the left side of a table until it finds a cell that matches your reference cell, then deliver the result of a cell in column X (to it's right).

VLookup is written like this: =vlookup (A1,A1:C5,3,false)
    A1=lookup Cell
    A1:C5 = Lookup Table
    3 = Reult from column 3
    false = deliver only if exact match

For your file,

(with both files open) write this formula in the destination cell (K2 in this example):

VLOOKUP(B2,'[High-Schools-2014-with-PAAK-code.xlsx]Sheet1'!$A$1:$K$687,11,FALSE)

you then can copy that formula down through the sheet.  Any non-matches will result in #n/a.

The challenge I see however, is you need to have the school names match exactly.  An abbreviated, or alternative name will not result in a match.

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

 

Author Comment

by:gsfc
ID: 39924480
Started off ok, but am receiving #N/A (which I expected) or #REF!  The paak code is found in the High-Schools-2014-with-PAAK-code.xlsx.  Why is it not returning the code?
0
 

Author Comment

by:gsfc
ID: 39924510
Oops, my fault.  Didn't use absolute cell reference in the formula.  This works like a charm!  Thank you so very much!
0
 

Author Closing Comment

by:gsfc
ID: 39924513
Wonderful idea, and will be extremely useful in the future.  Also, once I copied down the formula and had the codes in, I copied then pasted special to include just the values so I don't have to rely on the link to the other spreadsheet.  This works for what we needed to do.
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

Suggested Solutions

Title # Comments Views Activity
Help with merging Excel Documents 6 47
Excel VBA: Nested Areas in a Range object ? 6 75
Excel Automation of Autosum 23 99
why can't I sort this by internment date 1 42
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
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 the basics of the new Quick Analysis feature in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

920 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