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

Form Entry ID	Name	Test ID	Date	Location
	Student 1	12345	11/5/2015	California
	Student 2	12346	11/5/2015	Texas
	Student 3	98765	11/5/2015	Illinois
	Student 4	98764	11/5/2015	New York
	Student 5	56789	11/5/2015	Nevada
	Student 6	54321	11/5/2015	Georgia
	Student 7	54322	11/5/2015	Florida

<tbody>
</tbody>

Sheet 2

Form Entry ID	Name	Test ID	Date	Location
101	Student 1	#12345, Student 1 took test 12347 as well	11/5/2015	California
102	Student 2	12346, 12348, 12349	11/5/2015	Texas
103	Student 3	98765 98764 (Student 3 & Student 4 worked together)	11/5/2015	Illinois
104	Student 4	98764 and 98766	11/5/2015	New York
105	Student 5	56789and56790	11/5/2015	Nevada
106	Student 6	54321passed	11/5/2015	Georgia
107	Student 7	54322 passed	11/5/2015	Florida
108	Student 7	54322 retake	11/5/2015	Florida

<tbody>
</tbody>

Desired Output Example

Form Entry ID	Name	Test ID	Date	Location
101	Student 1	12345	11/5/2015	California
102	Student 2	12346	11/5/2015	Texas
103	Student 3	98765	11/5/2015	Illinois
103	Student 3	98764	11/5/2015	Illinois
104	Student 4	98764	11/5/2015	New York
105	Student 5	56789	11/5/2015	Nevada
106	Student 6	54321	11/5/2015	Georgia
107	Student 7	54322	11/5/2015	Florida
108	Student 7	54322	11/5/2015	Florida

<tbody>
</tbody>

 

[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