Merging two worksheets and setting a migration date

[bmas56]

Related to my first post (for which I thank JeffreyBrown, see
http://www.excelforum.com/excel-gene...ing-names.html for the context), I am now looking for another capability, which I anticipate using often.

In the first instance of it, I want to match names in two work sheets and enter a "migration date" in the first sheet that comes from the second sheet. One wrinkle: there are already plenty of dates listed in the first sheet. The dates from the second sheet may override the dates in the first sheet, but if there is not a matching name in the second sheet, any other date related data needs to remain instact. For example, if I have a name Vince Wilfork in the first sheet and the second sheet, and on the second sheet, I have Vince listed to migrate on 6/21/2011, that data should be inserted by the procedure onto the first sheet.

If there are no matching dates on the second sheet, then no value should be inserted or modified on the first sheet, because some dates are already filled in.

Is there a function, a group of functions, or a simple macro to do this? - and it would be great if it could be a function or macro that could be applied to the entire worksheet because it is 18-20,000 records long, has sparse data entered in some of these fields already, so "going to the bottom of the column" doesn't always work.

Once again, I appreciate the help that I've already been given by Jeff; hopefully I can get fairly quick turnaround on this one; I am thankful for what's already been shared, and I'll be thankful for helpful tips once again. ---Brian

[MarvinP]

Hi bmas56,
I used two helper columns and think I have an answer for you.

See the attached.

[bmas56]

I may have difficulty scaling this solution to the size of my spreadsheet database, which has nearly 20,000 entries overall - and there are some columns within each row that have populated columns, but others don't, so copying and pasting the formula to all columns concerning dates (or any other field I where I may need a formula, for that matter) can be a problem.

Can you think of any way to express the lookup or match so that it can be applied to every row in the spreadsheet, or perhaps a way to organize the data differently?

That said, it does look like this approach is one possible working solution, so thank you very much for providing it to me. I really appreciate it!

[bmas56]

I tried the formula you provided, Marvin. It worked in the very simple case of the sports spreadsheet, but in the actual database I have, the name is in column A, ,as is the name in the sports example, but the date - a migration date, is in column M in the main spreadsheet (which also has around 20,000 rows), and the date that I want to extract is in column G in this particular worksheet to merge (but that may not necessarily be the case for a merge that I may need to do next week).

I guess what I need to better understand is how I can make these rows and columns flexible and variable without having them lose their context and accuracy. In some cases, I may want to add an entire row from the second worksheet if the name doesn't exist in the first worksheet.

That's not what I need today though. Today, what I need is to match the names (which are both in column A for worksheet one and worksheet two). I want to take the date that's in column G of worksheet 2 for matching rows, and place it in column M in worksheet one.

I can create another spreadsheet simulating this if you need it. The data in columns B-L in worksheet 1 needs to be preserved, but it does not factor into any computations. The data in column M should not be overwritten if there is a date already present (but we should have something simple to remove if we DO choose to overwrite values).

Is this understandable? I've tried to create a new contrived spreadsheet that is closer to what I actually have in the real data. The main differences between the contrived spreadsheets and the real data is that the real data has about 20,000 rows and about twenty six columns, but the name is in the first column (A) and the date is in column M, just as in the example.

[MarvinP]

Hi Brian,

If you merge or append data onto your existing data, you will need to get the data in the correct columns for the formulas to work. I suggest you import into a new sheet and then manually copy and paste it onto your working sheet to insure the columns match.

I think last spreadsheet's formulas will work if you get the column data correct.
As for the 20000 rows. You need to read about AutoFill or dragging formulas down.
http://support.microsoft.com/kb/291359 or
http://www.jegsworks.com/Lessons/num...s/autofill.htm

[bmas56]

I'll give that a try and see how it works out. Thanks!

[bmas56]

I want to express my thanks for the help. I did get a satisfactory solution; thank you very much!

[bmas56]

I have gotten a LOT of mileage out of the help that was given earlier, and I have been able to apply it to a number of different scenarios, just as I had hoped and expected, so THANK YOU VERY MUCH!

I have one more twist to the merge case: this time, I have a list of accounts that I want to migrate to a new system, and I have a feed of the Active Directory users that currently exist. I have already figured out how to identify those entries in the list of Accounts to Migrate, and also to create a merged list of both the Accounts to Migrate and the Active Directory users.

The one remaining thing I have left to do - and maybe it's just because it is late in the day and I have a "brain freeze", but what I want to do is take all data in the Active Directory that is NOT included in the Accounts to Migrate and add all those Active Directory rows not already in the Accounts to Migrate. I have all the information I need, and I have the Active Directory in spreadsheet form.

How do I identify Active Directory rows not in the Accounts to Migrate and add each row to the Accounts to Migrate that is in the Active Directory and not in the Accounts to Migrate without duplication or error?

Attached is a contrived example for use in this discussion.

[MarvinP]

You need to find 2 common cells between the two worksheets and use VLookup(), would be my guess.

Your example file didn't make much sense to me.

[bmas56]

You need to find 2 common cells between the two worksheets and use VLookup(), would be my guess.

Your example file didn't make much sense to me.

I already have the merged data for these two workbooks, and I used VLOOKUP as mentioned previously to get the merged data.

Here's the problem; I'll try to articulate it differently; hopefully that will help.
The Active directory workbook has a couple thousand more entries than the Accounts to Migrate workbook. However, the fields in the two workbooks are not a one to one match, though there are six or seven common fields.

What I want to do is to append to the bottom of the Accounts to Migrate all of the rows that are in the Active directory but not in the Accounts to Migrate. In my attached example, the workbook with the larger number of entries is the Active Directory.

I don't know if a handy macro could be created to navigate this stuff once I get it created; that would be handy, too, but I am not yet versed in macros at all. I have, however, managed to get the VLOOKUP, thanks primarily to earlier help, down quite well. I create the first VLOOKUP, then modify the parameters for the other columns needed, then I copy and paste the data for all the rows.

What I don't get is how I can add only the NEW data in the Active Directory to the Accounts to Migrate workbook. The VLOOKUP patterns I have been using give me all data in both workbooks, and give me #N/A values where the Accounts to Migrate workbook has rows no longer in the Active Directory. I used filters to get rid of those. Maybe I just need to do a bit more of that, but perhaps in discussing it, either someone will come up with a new handy tip, or maybe "The Light" will finally come on and I'll figure it out myself. I nearly had it earlier today, but then I got stuck, so any ideas are still appreciated. Thanks.

[MarvinP]

So Sheet1 has 1000 rows and sheet2 has about 50 rows. You do a VLookup from Sheet1 to Sheet2. All the #N/A are the ones you need to copy over from Sheet1 to Sheet2. Is that the problem?

A possible similar problem to this was the MissingNumbers problem The User had a list of 10000 numbers but some were missing. He asked how he could find out which were missing. We created a list of all numbers and did a VLookup into the smaller List. Sort the list and the #N/A come to the top as missing. See attached.

[bmas56]

So Sheet1 has 1000 rows and sheet2 has about 50 rows. You do a VLookup from Sheet1 to Sheet2. All the #N/A are the ones you need to copy over from Sheet1 to Sheet2. Is that the problem?

A possible similar problem to this was the MissingNumbers problem The User had a list of 10000 numbers but some were missing. He asked how he could find out which were missing. We created a list of all numbers and did a VLookup into the smaller List. Sort the list and the #N/A come to the top as missing. See attached.

Thanks! I will take a look at it and see if I can apply this to my situation. I'll let you know the outcome, probably some time this afternoon.

[bmas56]

I think I've got it, based on the idea of finding the mismatched values. It still seems pretty cumbersome, however, to have to do all of this manipulation every time I do this exercise.

Is this a good place to ask how to write macros to take care of the cumbersome steps, or could you suggest to me another resource where I might ask that question?

I think I am zeroing in on this. I just want to make the process smoother and faster, and also, once I get it right, RELIABLE, so a mistake anywhere along the way in copying data around won't create an incorrect result.

I'll let you know if I get it based on this Missing Numbers algorithm, and I'll still reach out to see if anyone has some clever ideas to make the process of moving the data around and getting the missing rows to add, that would sure help.

Thanks a bunch!