3 days and still no luck. Need Excel Formula or VBA help please!!! Matching Data

[manvel001]

I've been playing around with this for 3 days now and still no luck. Everyone I talk to has a different method (VLOOKUP, HLOOKUP, INDEX & MATCH) but none to seem to work just right. Any help would be greatly appreciated before I pull my hair out. Here is what I am trying to accomplish.

I would like to display the Form Entry ID..IF the Test ID from Sheet 1 matches the Test ID from Sheet 2. The Test ID field from sheet 2 is a user entry form so there is no consistent way the data is entered and there may be duplicates which is fine.



Sheet 1

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Form Entry ID[/TD]
[TD]Name[/TD]
[TD]Test ID[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 1[/TD]
[TD]12345[/TD]
[TD]11/5/2015[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 2[/TD]
[TD]12346[/TD]
[TD]11/5/2015[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 3[/TD]
[TD]98765[/TD]
[TD]11/5/2015[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 4[/TD]
[TD]98764[/TD]
[TD]11/5/2015[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 5[/TD]
[TD]56789[/TD]
[TD]11/5/2015[/TD]
[TD]Nevada[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 6[/TD]
[TD]54321[/TD]
[TD]11/5/2015[/TD]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student 7[/TD]
[TD]54322[/TD]
[TD]11/5/2015[/TD]
[TD]Florida[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Form Entry ID[/TD]
[TD]Name[/TD]
[TD]Test ID[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Student 1 [/TD]
[TD]#12345, Student 1 took test 12347 as well[/TD]
[TD]11/5/2015[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Student 2[/TD]
[TD]12346, 12348, 12349[/TD]
[TD]11/5/2015[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Student 3[/TD]
[TD]98765 98764 (Student 3 & Student 4 worked together)[/TD]
[TD]11/5/2015[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]Student 4[/TD]
[TD]98764 and 98766[/TD]
[TD]11/5/2015[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]Student 5[/TD]
[TD]56789and56790[/TD]
[TD]11/5/2015[/TD]
[TD]Nevada[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]Student 6[/TD]
[TD]54321passed[/TD]
[TD]11/5/2015[/TD]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]Student 7[/TD]
[TD]54322 passed[/TD]
[TD]11/5/2015[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]Student 7[/TD]
[TD]54322 retake[/TD]
[TD]11/5/2015[/TD]
[TD]Florida[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output Example


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Form Entry ID[/TD]
[TD]Name[/TD]
[TD]Test ID[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]Student 1[/TD]
[TD]12345[/TD]
[TD]11/5/2015[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]Student 2[/TD]
[TD]12346[/TD]
[TD]11/5/2015[/TD]
[TD]Texas[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Student 3[/TD]
[TD]98765[/TD]
[TD]11/5/2015[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]Student 3[/TD]
[TD]98764[/TD]
[TD]11/5/2015[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]Student 4[/TD]
[TD]98764[/TD]
[TD]11/5/2015[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]Student 5[/TD]
[TD]56789[/TD]
[TD]11/5/2015[/TD]
[TD]Nevada[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]Student 6[/TD]
[TD]54321[/TD]
[TD]11/5/2015[/TD]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]Student 7[/TD]
[TD]54322[/TD]
[TD]11/5/2015[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]Student 7[/TD]
[TD]54322[/TD]
[TD]11/5/2015[/TD]
[TD]Florida[/TD]
[/TR]
</tbody>[/TABLE]

 

[ramulose]

I can help you do this with vba code. It's not terribly complicated, although it will take a fair amount of code.

That being said, your 3rd requirement, real time updating, Is not something I would recommend. It requires capturing the worksheet.change event. It's not difficult to do, but I always try to avoid events as much as possible. It basically runs some part of your code every single time any piece of data is added or changed anywhere on your spreadsheet. Each time a new piece of data is entered or changed, you have to check to see if the change has anything to do with your functionality, and then execute your code accordingly.

Again, it's not all that complicated to do, it just comes with a lot of overhead.

 

[manvel001]

I can help you do this with vba code. It's not terribly complicated, although it will take a fair amount of code.

That being said, your 3rd requirement, real time updating, Is not something I would recommend. It requires capturing the worksheet.change event. It's not difficult to do, but I always try to avoid events as much as possible. It basically runs some part of your code every single time any piece of data is added or changed anywhere on your spreadsheet. Each time a new piece of data is entered or changed, you have to check to see if the change has anything to do with your functionality, and then execute your code accordingly.

Again, it's not all that complicated to do, it just comes with a lot of overhead.

If real time is too much trouble that's fine, I just appreciate you taking the time to look at it. Please note that Sheet 1 currently gets populated from a SQL ODBC Database connection. Not sure if that matters or not, but I just hit the refresh button on sheet 1 to pull the latest data from the database.

 

[ramulose]

Have you worked with VBA code before? Do you know how to open the Development Environment, create a module, etc? I just need to know how basic I need to get

 

[ramulose]

Another question. One student can obviously take more than 1 test (as you show for student 7 on sheet 2). In your Desired Output, you show 2 entries for Student7, but on Sheet 1, you only show 1 entry for student 7. Are you expecting the program to duplicate Student 7 on sheet 1 if there is more than 1 test taken for a particular student, or will the second entry for Student 7 have already been created by your database update?

 

[manvel001]

Another question. One student can obviously take more than 1 test (as you show for student 7 on sheet 2). In your Desired Output, you show 2 entries for Student7, but on Sheet 1, you only show 1 entry for student 7. Are you expecting the program to duplicate Student 7 on sheet 1 if there is more than 1 test taken for a particular student, or will the second entry for Student 7 have already been created by your database update?

Yes, I am expecting the program to duplicate Student 7 on sheet 1 if there is more than 1 test taken for a particular student. Yes, i have worked with VBA a little bit, so i know how to apply the code just not so good writing the code. I know how to get to the dev environment and create an module etc.

 

[ramulose]

In order for this to work, the names on sheet 1 in the 'Name' column have to match EXACTLY with the names on sheet 2, and there can't be more than 1 student with the same name. 'Name' is going to be the key on which you have to match to know which student to apply the 'Form Entry ID's" to. Is this a problem?

I just want to make sure you've thought through all of this before we embark on an adventure that we can't complete.

 

[ramulose]

The more I think through this the more I see that you have some things you need to solve for.

1. Will the entries in sheet 2 still be needed after Sheet 1 is updated? Would it be ok to remove the row from sheet 2 after it's processed. If not, you need some way to be able to identify which rows have already been processed and which haven't.
2. Do you expect people to be able to update an entry from sheet 2 if they made a mistake, like entering a wrong test number?

 

[manvel001]

The more I think through this the more I see that you have some things you need to solve for.

1. Will the entries in sheet 2 still be needed after Sheet 1 is updated? Would it be ok to remove the row from sheet 2 after it's processed. If not, you need some way to be able to identify which rows have already been processed and which haven't.
2. Do you expect people to be able to update an entry from sheet 2 if they made a mistake, like entering a wrong test number?

Hello again,

1. Yes, the data in sheet 2 is populated from another data connection(webpage) which is also set up to refreshed on demand.

2. Yes

 

[ramulose]

With your last 2 answers, it's clear that you really need a full-blown program to be built. Just as an example, in order to be able to update an entry that has already been processed on sheet 2 and created on sheet 1, you have to create some unique identifier to connect the original entry on sheet 2 to the matching row in sheet 1. Otherwise you have no way of knowing if the entry is an update or a new entry that needs to be created.

This has grown into a big project. It's outside of the scope of what I am willing to do here. I don't know if you have a budget to work with, but I know MrExcel has consulting services available. You may want to check with them. You have a pretty good start on defining your requirements through this thread so they may be able to give you an estimate pretty easily.

Sorry about that.

 

[manvel001]

Any other takers on a resolution?