Comparing and Merging data

S

Scott

Hi. I am doing a sociology project that compares the distance from school to
their grade. I have two sets of data- OSIS (student ID) and Grade and OSIS
and Zip code. It would be simple, however, the set with the zip code has
more data than the one with the grades (since freshmen dont have grades). Is
there a way to take only the data that is in both sets of data and put it in
another colum like this:

OSIS Zip Grade
200111417 11221 65.77
200544351 11220 65.17
200555449 11235 92.93
200570539 11204 83.33

Thanks.
 
B

bpeltzer

Four steps:
1) Copy the larger set of data (if the data sets on on different worksheets,
just right-click on the worksheet tab containing the zip codes and move/copy
the entire sheet).
2) On the new copy, use a VLOOKUP function. The formula in the first row
where you have the zip code will be something like
=vlookup(OSIS_cell_reference,OtherSheetName!A:B,2,false). After you get the
formula right, autofill to copy it to each row in your copied worksheet.
3) Select the entire data set, Edit > Copy, then Edit > Paste Special and
select 'values' to lock in the values instead of the formulas.
4) Data > Filter > Autofilter, and use the drop-down to select '#N/A' in the
column of data where you looked up the grades. Select the visible rows and
delete them.
 
Top