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]

 

[Ombir]

As your data is inconsistent, can you upload your excel file anywhere on web and share link to download with us. That would be really Useful. However, as per sample shared by you, a helper column is needed in Sheet 2 with formula =left(c2,5) assuming test id starts from C2 in sheet2. Then you can use index match to perform right to left lookup. This will find maximum of your form entry ID. Hope this helps !

 

[manvel001]

This seems to partially work =IF(ISERROR(FIND(C2,Sheet2!C:C,1)),"Item Not Found",Sheet2!A2) but not consistently. I'm not sure if duplicates are handled well or not. If i enter a Test ID manually into C2, it does not seem to display the Form Entry ID. I'll post the excel data later this afternoon.

 

[ramulose]

One of the cells in sheet 2 contains the data '#12345, Student 1 took test 12347 as well'. I assume you want your app to pull the 'Form Entry ID' from sheet 2 if either '12345' is entered in sheet 1, or '12347' is entered. Is that correct?

Also - Will the 'Test ID' ALWAYS be 5 characters?

 

[manvel001]

Hi ramulose, I would like the app to pull the 'Form Entry ID' from sheet 2 only if the the 'Test ID' exists on Sheet 1. In this case it would only pull the 'Form Entry ID' for '12345'. The 'Test ID' will always be 5 characters on sheet 1. Users enter the 'Test ID' on sheet 2 which is the problem because the enter in other information as well which is not always consistent. Hope that helps.

 

[manvel001]

see below

 

[manvel001]

As your data is inconsistent, can you upload your excel file anywhere on web and share link to download with us. That would be really Useful. However, as per sample shared by you, a helper column is needed in Sheet 2 with formula =left(c2,5) assuming test id starts from C2 in sheet2. Then you can use index match to perform right to left lookup. This will find maximum of your form entry ID. Hope this helps !

Hello, here is the attached file. Thanks again <a href=http://www.filedropper.com/testdata><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >share files free</a></div>

 

[SpillerBD]

You need to strip down the Manual Test ID data that is inconsistent. The form should have memos or comments entered in another field.
However see http://1drv.ms/1RAxE8l

 

[ramulose]

Ok - I think we're getting there . A few more questions.

1. The 'Desired Output Example' table that you show, is that a different sheet, or are you updating sheet 1 with the data to make it look like that?
2. What does sheet 1 actually contain? Is it pre-populated with the Name, Test ID, Date, and Location for each student, or does it get populated as data is entered in sheet 2?
3. Are you expecting real-time updating? In other words, do you expect the 'desired output' to be created each time data is entered into table 2? Or will it be updated as a batch process? (i.e. someone runs a macro periodically to create the desired output?

 

[manvel001]

Ok - I think we're getting there . A few more questions.

1. The 'Desired Output Example' table that you show, is that a different sheet, or are you updating sheet 1 with the data to make it look like that?
2. What does sheet 1 actually contain? Is it pre-populated with the Name, Test ID, Date, and Location for each student, or does it get populated as data is entered in sheet 2?
3. Are you expecting real-time updating? In other words, do you expect the 'desired output' to be created each time data is entered into table 2? Or will it be updated as a batch process? (i.e. someone runs a macro periodically to create the desired output?

Hello again ramulose
1. I'm just updating sheet 1 with the data to make it look like that. Ideally Column A(Form Entry ID) of Sheet 1 which is currently blank, would contain the correct formula.
2. Sheet 1 is pre-populated with the Name, Test ID, Date, and Location for each student
3. I'm expecting real time updating