Joining two sheets or ranges using a primary key?

**jhigh** · 01-10-2009, 11:55 AM

I'd like to know how to join to sets of data by one field that is shared between the two sets, the field name is 'acct'. I'll call the them Set A and Set B.

If 'acct' in Set B and not in Set A then join to Set A.

If 'acct' in Set A and not in Set B, then delete.

Delete any duplicates coming from Set B and keep only rows from Set A.

My need for this stems from account inventory tracking month to month. I have 300+ accounts in a spreadsheet, with about 20 columns of info per account. 19 columns are user input that I need to keep month to month. So I have a sheet built in December with all of the data I need to keep. January comes and I only have a single column of account numbers with no extra data. 70% of the January account numbers already exist in my December sheet. I want to join them up while keeping the December data in the user input fields, while identifying a new account. I also want to delete and accounts that are in December and NOT in January. I've been sorting by account number and de-duping with a basic formula, but it is very time intensive, completely manual.

How can I automate this? It's more likened to a database problem. I used to do this all the time with the SAS language, I just can't peg it in Excel.

I hope I'm making sense, this is why I reduced it to simple logic first.

Thanks for any assistance!!!

**JBeaucaire** · 01-10-2009, 02:07 PM

If you add items from Set B missing INTO Set A, then delete items in Set A NOT in Set B, the end result exactly equals Set B.

Knowing this, can I say it this way: "I need my new Set B to bring over all the matching data from the previous month automatically, and the new accounts won't have any data because they're new....I'll fill those out for this new month?"

If so, set your Jan Sheet with the exact same columns and headers. Then put the new account numbers into the correct columns and use a standard VLOOKUP to draw data over from the previous sheet.

When you copy that down to your whole new list in Jan, the new accounts will show a #N/A error because they're new. Fill out those cells manually with the new data.

Next month, same process.

On sheet Jan, in B2 (let's assume that's the first row with an account # in A2 to be examined), the formula in B2 would be something like:

=VLOOKUP($A2,Dec08!$A$2:$B$350,2,FALSE)

Now expand that by one column and put this in C2 to get the next piece of data:

=VLOOKUP($A2,Dec08!$A$2:$C$350,3,FALSE)

When the formulas in across the first row are complete, copy them down to complete the data transfer to the new month, then fix the #N/A errors for your new accounts by entering the correct new data.

**dburkard** · 11-27-2010, 08:18 PM

I have 2 sheets each with a 'unique ID' that is common between the 2 sheets, but the rest of the data in each sheet is different and I need to present relevant columns & rows from each sheet in a new sheet.

rows would be based on the shared unique number

some or all columns could be moved to the new sheet.

Would it be possible to further refine the data presented in the new sheet ... based on the values in a row ... ie dates or order numbers ... all after a certain date or all order number between two numbers ... if this is possible, how do you do it?

**JBeaucaire** · 11-28-2010, 02:31 AM

dburkard, ... Welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively.

For instance, posting questions of your own in someone else's thread is a no-no, and also guarantees most forum contributors won't even see it, since new threads get the most attention. Post your own thread.

Joining two sheets or ranges using a primary key?

LinkBack

Thread Tools

Rate This Thread

Display

Joining two sheets or ranges using a primary key?

Re: Joining two sheets or ranges using a primary key?

Re: Joining two sheets or ranges using a primary key?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions