How do I match number IDs from 2 worksheets - when one worksheet is only a subset of ids from the other?

April 17, 2012 5:58 AM

Excel help: How do I match number IDs from 2 worksheets - when one worksheet is only a subset of ids from the other?

I have two worksheets that have a common # id. Worksheet A includes the entire population of cases and includes all the # ids. Worksheet B includes only a subset of cases and includes only those # ids. (A is a list of all cities, B is a list of only certain cities - the # id is the Census #, if relevant). Worksheet A has additional information that I want to merge with B - but I only want those with # ids on B. How do I match up the #s?

posted by quodlibet to Technology (6 answers total)

If its formatted correctly in sheet A use vlookup - if the ID's in worksheet A are numeric and ordered that'll work fine. If the codes are more complicated or not sorted I'd normally use a match function to find the row of that identifier in table A, and then an offset function to pull the data into table b

I'm guessing there is a more elegant solution to match and offset these days. It will get the job done though.
posted by JPD at 6:05 AM on April 17, 2012

Alternately and even easier is if all of B is in A, and B is just defined by a list of cities and includes all those cases in a city - I'd just make a pivotable. Even easier is just using the filter function.
posted by JPD at 6:07 AM on April 17, 2012

Not 100% clear what you're trying to do, but you might want something like this:

=VLOOKUP(A2,SheetA!A:B,2)

Where that's a formula in a new column of "worksheet B", A2 is a cell on worksheet B that contains the ID, SheetA is "worksheet A", column A on SheetA has the ID, and column B on SheetA has some information you're trying to get from "worksheet A" into "worksheet B".
posted by Perplexity at 6:07 AM on April 17, 2012

I would just export the table into Access and do a join.
posted by sarahnicolesays at 8:19 AM on April 17, 2012 [1 favorite]

Vlookup is a good option. Here is another: COUNTIF
If you want all columns from Sheet A copied to Sheet B I think it would be the fastest way as well. Assuming IDs are in column A on both sheets, insert a column before column A on Sheet A (now your ids are in column B). In cell A2, put this formula: "=COUNTIF(SheetB!A:A,B2)" and paste that formula all the way down. That will give you a 1 if the id exists on Sheet B and a 0 if it does not. This also assumes you have a header row in Row 1 and not data. Now copy column A and paste over it using Paste Special... and pick Values. That will change the formulas to the actual numbers. Put your auto filter on and filter column A for 1. Now you can copy the columns you need from A including ID and paste them over onto Sheet B. If you have more data on Sheet B, you will need to make sure the ids are in the same order. If they aren't, sort B before you paste the new data in and leave a blank column between the current Sheet B data and the stuff from A. Highlight just the columns you pasted from A and sort that by ID as well. Now eyeball it to make sure both id columns match before you delete the blank column and the second id column.
posted by soelo at 10:36 AM on April 17, 2012

Agreeing with sarahnicolesays, this is so easy with a database. You began in Excel, but you are at a stage where you want to do things more appropriate to a database. Happens a lot.
posted by exphysicist345 at 5:40 PM on April 17, 2012