The other is a bigger list of names, including all the names in the first spreadsheet (but with additional names as well), with a numerical value in the next column over–one value for each name.
I would like to create a third spreadsheet which contains a column of just the smaller list of names, but with (in the next column over) the numerical value for that name from the second spreadsheet.
So for example, if my first spreadsheet is just:
John Doe
Jane Moe
And my second spreadsheet is:
John Doe | 5
Jim Roe | 3
Jane Moe | 9
I would like the final spreadsheet to be:
John Doe | 5
Jane Moe | 9
Of course the actual case I’m talking about involves much longer lists, such that doing it by hand would be fairly arduous.
Sure. Copy and paste your first page list to the third page. Now you have all the names. Then do a V-Lookup for the names against the list on the 2nd page. Your numbers will then be on the third page next to the name.
I’ve always had it look up the specific cell value – is that not necessary? It looks for the first “a” in Sheet 2 column A and returns what is in the second column:
a 1 [ +VLOOKUP(A1,Sheet2!A:B,2,FALSE) ]
s 2 [ +VLOOKUP(A2,Sheet2!A:B,2,FALSE) ]
b 3 [ +VLOOKUP(A3,Sheet2!A:B,2,FALSE) ]
d 4 etc.
No, you’re right. The lookup value is usually a single cell. I was writing the formula from memory and not looking at excel.
jonesj2205 - You don’t have to have the cells in alphabetical or numerical order if you’re using absolute values in the array field. It looks at all those same fields for each lookup value. So [ =vlookup(a1,Sheet2!$A:$B,2,False)] will look at the whole columns A and B for each lookup value.
Okay, generalizing the problem: Can I do something similar, but with more than one column of values? I can see how to do it with a separate vlookup in each cell in question. I’m hoping for something more streamlined if it exists.
It appears what I’m ultimately after is something like what gets done by JOIN in SQL, if that helps.
(I say that being mostly ignorant about SQL, it’s just a connection that came up as I was googling.)
Say, you can’t use SQL to make two excel spreadsheets talk to each other, can you? Just in excel itself? (wishful thinking, crossing fingers)
Keep in mind that SQL is a language developed to control/modify databases. While a multiplicity of Excel spreadsheets can weakly mimic an actual database, Excel is not a database program.
The response St. Germain gave in post #2, the VLOOKUP function, is how Excel mimics the JOINs and WHERE clauses of SQL. To bring in more than one column of values, you need more than one VLOOKUP function – one for each column you bring in. Taking St. Germain’s sample formula as a starting point, the data set selected from (the “array” is one term for it) needs to be defined with more columns, and the number after it needs to change. You’d end up with multiple VLOOKUP functions like so (assuming the larger data set has a header row and 300 values, and that the smaller data set has four fields you want populated in Row 2):
(only the blue text above represents the actual formulas. The dollar signs in the array designation keep the array from changing should you copy these VLOOKUP formulas down into other rows)
You’re starting to bump against some of the reasons spreadsheet-based data processing gets ditched in favor a full-blown database. Databases can do a lot of this kind of analysis more cleanly and with less user intervention. Yes, there are offices all over that have a resident Excel guru who’s built impressive edifices of formulas, macros, and Visual Basic scripts. Those edifices get unwieldy quickly, however, and are prone to corruption and to difficult-to-trace failures.
Thanks for that information. One of the causes of the problem(s) I’m running into is that the only way I can interface with any of this information is through excel spreadsheets automatically generated from a database management system we use here. I have no control over what reports are available, I have to take what I can get, and then use Excel to piece together what I want.
They’re strapped for time and money, and apparently no one here knows SQL anyway (the one who did is gone). As far as I can gather, SQL is what would be needed to create new types of reports.
I suggested maybe I could learn the basics and go in and do the things I need myself, but they laughed me out of the room. (Literally, loudly.)
BTW I tried what presently looks to me like exactly what you suggest and got a bunch of errors ("#N/A") in every cell I put the formula in. But the similarity of what I was trying to what you just typed up makes me think I should keep working in that direction, so that’s good. Just a bug, rather than a completely wrong approach.
Frylock, I don’t know your level of expertise, so this advice could be helpful, or it could be “please – who DOESN’T know that?”:
Those formulas I wrote in blue text above … those are only samples. The exact formula that will work in your spreadsheet will depend on how the data is situated in your actual Excel spreadsheet/tabs. Sheet names like “Sheet1” and “Sheet2” are only defaults – they could be named anything else in your file. The specific columns (A, B, C, etc.) in your data might not be A through E. And so on.
An additional note about matching names across two Excel tabs/spreadsheets: they must match EXACTLY. Any differences at all will result in a “no match”. Common causes of “no matches” are commas, extra space character before/after a name, misspellings, change in FirstName/LastName order, etc.
Who’s “they”? A lot of database admins jealously guard their data, and think access to non-experts is “dangerous”. IME, this danger is rarely present when novices aim to view (as opposed to modify) data.
SQL, for the purposes of pulling data out of a database, does not have much of a learning curve at all. Better yet, there are many third-party software options that will build & execute the SQL statements for you through a drag-&-click interface (if you’ve heard of Crystal Reports, that’s a popular one).
There’s no real-world reason for your database folks to be obstructionist about it, unless they are trying to avoid not much extra work on their end. Or else they have a poor understanding of how to erve their data “customers”.
BTW, it’s well-known that unchanging canned reports are a lousy way to deal with data. In most environments, data needs change with some frequency. You’re not pulling the same kind of data for years on end without changes in expectations, business rules, etc. An inflexible database administrator is a bad database administrator.
Access will allow you to import Excel data into tables, which you can then tie together using SQL.
But you can easily do more than 1 Vlookup. I have one report that I run where I’ve written a macro to reformat columns, run 10 Vlookups from several worksheets. All this collating takes the macro less than a minute to do.
I don’t think so. It just looks for the first one it finds in the column you tell it. This does become an issue if you have the same name more than once with a different value in the second column, since the lookup will return the first one.
There’s a lot of great input above.
I just thought I’d call out as I wander by that…
Excel includes ODBC (i.e. database) functions which can be explicitly invoked.
Teach Yourself SQL In 21 Days is a popular way to get your feet wet. You might even like building simple queries. More complex queries may take more time and effort. There are also other books, and a few on-line tutorial websites. I used a combination of all three and can do okay for some simple data-dumps (like what you’ve asked about). We have contractors who do the major financial reports, and once in a while they give me tips and lessons.
I think if you’re smart enough to contribute regularly to The Dope boards (and I’ve seen you around for quite a while) you’re smart enough to dabble in SQL a bit. You might end up finding another way to get your solution for the original question, but you’ll end up with another tool in your pocket with which to solve some other queries down the road.
Barring that, the quickest work around is to create a simple pivot table out of the longer list, then use the “GETPIVOTTABLE” function in your smaller list to grab the values and populate your field
Haha thanks for the pointers everyone. I’d never dealt with Access before but I’ve gone in and poked around a bit and I think this is going to be cake. (Overconfidence…)
My evil scheme is to design an SQL query that will assemble a full spill of all available information from the bits and pieces we get through our exported Excel reports, then from there I’ll be able to do whatever I want.
(This is probably naive–for all I know there’s too much there to let this be manageable, but anyway, learning experience.)
Are you sure you cannot get Access linked to the database directly - “LIVE” ?
Well the DB people may not want to give you full access to the database, but they can give you a read only “view” … linked to your access … the view would be the “select” of SQL, of only the fields you need, but it can include “join”… so right there the guts of your report is already done. You ask for a report linked to your Access, they do it in like 5 minutes…
