Combining two sets of data

**chrmlr2** · 08-27-2009, 08:03 PM

Hi this is my first time posting in these forums, I'm helping a non-profit organization enter their kids into a database.. I'm trying to combine two sets of data. I have already entered the first set which is students first and last names. I have another excel document that has their id numbers (as well as their first and last names). Is it possible to create a function that will search the entire second document and if the students first and last names match copy the id number to a column in the first document? I appreciate the help, this would really be beneficial if this is indeed possible.

Also if it's easier I can copy the second document into separate columns in the first document.

I'm using excel 2007.

**NBVC** · 08-27-2009, 10:05 PM

Is the ID an actual number or is it alphanumeric?

**chrmlr2** · 08-28-2009, 12:21 AM

An actual number. Does excel have a search function where I could search through a selected area and perform an action if the search is successful?

**NBVC** · 08-28-2009, 07:46 AM

There are several functions that could be used.. but since you are returning a number and assuming there are unique records in the search database, then Sumproduct() would be the easiest to apply...

e.g

=Sumproduct(--('[Database.xls]Sheet1'!$A$2:$A$100=A2),--('[Database.xls]Sheet1'!$B$2:$B$100=B2),'[Database.xls]Sheet1'!$C$2:$C$100)

this looks for matches the Database.xls book within columnns A and B for matches to A2 and B2 first/last names and then returns value (ID) from column C.

**chrmlr2** · 08-28-2009, 11:57 AM

Thank you so much for the help. I didn't realize excel was capable of such things. Is it possible to to have the row number automatically update depending on which row the formula was in? So that I could assign it to a column rather than an individual cell?

**NBVC** · 08-28-2009, 11:59 AM

Not quite sure what you mean? Can you elaborate?

You know you can copy/fill the formula down a column and the cell references will automatically change to corresponding row, right? Is that what you mean?

**chrmlr2** · 08-28-2009, 12:28 PM

Originally Posted by NBVC

Not quite sure what you mean? Can you elaborate?

You know you can copy/fill the formula down a column and the cell references will automatically change to corresponding row, right? Is that what you mean?

That is what I meant, thank you so much for all your help.

**NBVC** · 08-28-2009, 12:43 PM

You're welcome,

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**chrmlr2** · 08-28-2009, 01:46 PM

The formula worked perfectly, I have a quick question. If I want to copy a word rather than a number for the id, what needs to be changed?

Also, if I email the excel document with the formulas, will the recipient need the second excel document the formulas refer to, or is there a way to finalize the formula results so that they cannot change?

**NBVC** · 08-28-2009, 09:41 PM

It would be a different formula entirely...

Using same parameters as before...

Please Login or Register  to view this content.

Note: This formula will work to pull numbers or text from C2:C100...

If you email the doc., the receiver must choose not to update links when asked...

Or you can copy the sheet and paste special over itself selecting Values and formats... this will keep the formats and change the formula results to hard coded numbers.