Copy relational records from one workbook to another

Brass Contributor

I have some records in a workbook that I need to copy into a new workbook that has records in it already. I have an id column in both workbooks, however, I can't just easily copy and paste because target records have extra records. I need to copy my source records to target based on related id.

 

Is there an easy way to do this because there are hundreds and I don't want to do it manually if I can help it or if you can help it, lol.

 

rodsan724_0-1635379057161.png

 

3 Replies

@rodsan724 

 

It's not altogether clear what you're trying to do (yes, I know, copy some records to another sheet based on ID), but the nature of the task has some unspoken assumptions, I think. Are you making more complete the partial records associated with some or all of the IDs? Or are you adding altogether new records of unique IDs? Etc.

 

Anyway, I pull records from one spreadsheet into another using the FILTER function. And you can further enhance or focus it with UNIQUE. Both of these new "Dynamic Array" functions require the newest version of Excel. Here's a link to a YouTube video launched by Microsoft to explain the functions. See if it points you in a productive direction.  https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
Sorry for confusion, I updated OP with an illustration.

@rodsan724 

 

I'm actually more confused based on that illustration. There doesn't seem to be a "relatedness" between the IDs, other than first digits (which is NOT what I'd call a real relationship). [Speaking as a person who had responsibility for the HR/Payroll database of a major corporation, so I do know relational databases, IDs and keys to a table and how to join them......]

 

That said, did you take a look at the video regarding FILTER? If so, does it offer any possibilities? If not, please explain further how these various records are related and what the bigger context is. And, since you appear to have gone to the trouble of creating actual spreadsheets to form the basis of the image, would you be able to post actual spreadsheets that are more complete mock-ups of the real situation? Actual spreadsheets are far more useful than an image.