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

M

manvel001

New Member
Joined
Oct 27, 2015
Messages
12
I've been playing around with this for 3 days now and still no luck:confused:. 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]
 
Sort by date Sort by votes
There should be more restrictions on what can be entered into what fields. That’s why we use validation cells where only certain things like a course number or a student’s id can be entered. To allow anything to be entered into a cell and then expect some sort of script to pull out the needed data is not a good plan. If the student wants to enter comments there should be a separate field for comments.
A new record should be created for every course completed or taken. To allow and entry such as “ took course 2345 and course 2346 with Mary Sue” also is surely not a way to run an entry form. And even a date field can be set up to only accept proper date entry’s.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
My Aswer Is This said:
There should be more restrictions on what can be entered into what fields. That’s why we use validation cells where only certain things like a course number or a student’s id can be entered. To allow anything to be entered into a cell and then expect some sort of script to pull out the needed data is not a good plan. If the student wants to enter comments there should be a separate field for comments.
A new record should be created for every course completed or taken. To allow and entry such as “ took course 2345 and course 2346 with Mary Sue” also is surely not a way to run an entry form. And even a date field can be set up to only accept proper date entry’s.
Click to expand...
Thanks for the reply and I completely agree, Im in the process of trying to get that implemented, the issue is i dont own the form. Once the field restriction is fully implemented I will still like to pull the form entry id in the first column. Any ideas?
 
Upvote 0
It's me again.

I totally agree with 'My Answer is This' (interesting user name BTW :cool: ) on trying to force a more structured way of entering data. The problem is, in many cases, (like yours) you have to figure out how to work with unformatted/unstructured data. We programmers love having things in neat little buckets so we don't have to worry about the strange idiosyncrasies of human behavior, but that can't always be done.

Below is some code that will allow you to extract the Test Numbers from the unformatted text, but that isn't really the hard part of this project. After you get the numbers, doing all of the matching on the rows in data in Sheet 1 by Student Name, copying all of the data into a new row, knowing if it is a new entry or one that has already been processed, etc, etc, etc, is where the real work starts.

The code should help you pull out the numbers, the rest is going to be a project :).

I know you don't know a whole lot about VB so I have HEAVILY commented the code. I hope it makes sense to you.

Code: 
Function GetTestNumbers()
    
    Dim TestIDs()
    Dim TestIDsString As String
    
    TestIDsString = "12345This99818 99281dd5"
    ' Call the funtion
    Call ExtractNumbers(TestIDsString, TestIDs())
    
    ' At this point you will have a list of the test numbers that are in the TestIDString string
    ' in the TestsIDs() array. You can find out how many test ID's were found using the
    ' Ubound fuction in VB. If the ubound value of TestIDs = 0 then no ID's were found.

End Function

Function ExtractNumbers(SourceString As String, TargetArray()) As String
    
    ' Initialize the variables
    ReDim TargetArray(0)
    Dim LenOfSourceString As Long
    Dim CharacterToExamine As String
    Dim CharPosition1 As Long
    Dim CharPosition2 As Long
    Dim NewNumber As String
    Dim TotalNumbersFound  As Long
    
    ' This routine will loop through the characters in SourceString and extract the
    ' numbers only. There can be any number of multiple-digit numbers in the string and
    ' all will be returned to the caller in the 'TargetArray()' array.
    
    ' Get the length of the string that has the numbers
    LenOfSourceString = Len(SourceString)
    
    ' Loop through each character in the string looking for a numeric character.
    For CharPosition1 = 1 To LenOfSourceString
        
        ' Get a single character from the string
        CharacterToExamine = Mid(SourceString$, CharPosition1, 1)
        
        ' Check to see if the character is numeric
        If CharacterToExamine >= "0" And CharacterToExamine <= "9" Then
            
            ' We found a character that is numeric so let's keep looking
            ' through the string one character at a time for more numeric characters
            ' so we can extract the whole number.
            For CharPosition2 = CharPosition1 To LenOfSourceString
                CharacterToExamine = Mid(SourceString$, CharPosition2, 1)
                
                ' Check to see if this is a numeric character
                If CharacterToExamine >= "0" And CharacterToExamine <= "9" Then
                    ' It is a numeric character so concatinate it with the one(s)
                    ' we already have and store it in a temporary variable.
                    NewNumber = NewNumber & CharacterToExamine
                Else
                    ' We have run out of numeric characters for this number
                    ' so stop looking.
                    Exit For
                End If
            Next
            
            ' ---- Store the number we extracted into the array so we can return it to the caller ----
            ' Keep track of the total number of numbers we have found
            TotalNumbersFound = TotalNumbersFound + 1
            
            ' Add a new entry to the array to hold the number.
            ReDim Preserve TargetArray(TotalNumbersFound)
            
            ' Store the number in the array.
            TargetArray(TotalNumbersFound) = NewNumber
            
            ' Reset the temporary variable that we are using to extract the numbers so
            ' we can reuse it to extract the next number.
            NewNumber = ""
            
            ' Set the character position we want to keep searching from, equal to the
            ' character position we left off with after building this number.
            CharPosition1 = CharPosition2
        
        End If
    
    Next

    ' Exit the routine and return the number(s) found

End Function
 
Last edited:
Upvote 0
ramulose said:
It's me again.

I totally agree with 'My Answer is This' (interesting user name BTW :cool: ) on trying to force a more structured way of entering data. The problem is, in many cases, (like yours) you have to figure out how to work with unformatted/unstructured data. We programmers love having things in neat little buckets so we don't have to worry about the strange idiosyncrasies of human behavior, but that can't always be done.

Below is some code that will allow you to extract the Test Numbers from the unformatted text, but that isn't really the hard part of this project. After you get the numbers, doing all of the matching on the rows in data in Sheet 1 by Student Name, copying all of the data into a new row, knowing if it is a new entry or one that has already been processed, etc, etc, etc, is where the real work starts.

The code should help you pull out the numbers, the rest is going to be a project :).

I know you don't know a whole lot about VB so I have HEAVILY commented the code. I hope it makes sense to you.

Code: 
Function GetTestNumbers()
    
    Dim TestIDs()
    Dim TestIDsString As String
    
    TestIDsString = "12345This99818 99281dd5"
    ' Call the funtion
    Call ExtractNumbers(TestIDsString, TestIDs())
    
    ' At this point you will have a list of the test numbers that are in the TestIDString string
    ' in the TestsIDs() array. You can find out how many test ID's were found using the
    ' Ubound fuction in VB. If the ubound value of TestIDs = 0 then no ID's were found.

End Function

Function ExtractNumbers(SourceString As String, TargetArray()) As String
    
    ' Initialize the variables
    ReDim TargetArray(0)
    Dim LenOfSourceString As Long
    Dim CharacterToExamine As String
    Dim CharPosition1 As Long
    Dim CharPosition2 As Long
    Dim NewNumber As String
    Dim TotalNumbersFound  As Long
    
    ' This routine will loop through the characters in SourceString and extract the
    ' numbers only. There can be any number of multiple-digit numbers in the string and
    ' all will be returned to the caller in the 'TargetArray()' array.
    
    ' Get the length of the string that has the numbers
    LenOfSourceString = Len(SourceString)
    
    ' Loop through each character in the string looking for a numeric character.
    For CharPosition1 = 1 To LenOfSourceString
        
        ' Get a single character from the string
        CharacterToExamine = Mid(SourceString$, CharPosition1, 1)
        
        ' Check to see if the character is numeric
        If CharacterToExamine >= "0" And CharacterToExamine <= "9" Then
            
            ' We found a character that is numeric so let's keep looking
            ' through the string one character at a time for more numeric characters
            ' so we can extract the whole number.
            For CharPosition2 = CharPosition1 To LenOfSourceString
                CharacterToExamine = Mid(SourceString$, CharPosition2, 1)
                
                ' Check to see if this is a numeric character
                If CharacterToExamine >= "0" And CharacterToExamine <= "9" Then
                    ' It is a numeric character so concatinate it with the one(s)
                    ' we already have and store it in a temporary variable.
                    NewNumber = NewNumber & CharacterToExamine
                Else
                    ' We have run out of numeric characters for this number
                    ' so stop looking.
                    Exit For
                End If
            Next
            
            ' ---- Store the number we extracted into the array so we can return it to the caller ----
            ' Keep track of the total number of numbers we have found
            TotalNumbersFound = TotalNumbersFound + 1
            
            ' Add a new entry to the array to hold the number.
            ReDim Preserve TargetArray(TotalNumbersFound)
            
            ' Store the number in the array.
            TargetArray(TotalNumbersFound) = NewNumber
            
            ' Reset the temporary variable that we are using to extract the numbers so
            ' we can reuse it to extract the next number.
            NewNumber = ""
            
            ' Set the character position we want to keep searching from, equal to the
            ' character position we left off with after building this number.
            CharPosition1 = CharPosition2
        
        End If
    
    Next

    ' Exit the routine and return the number(s) found

End Function
Click to expand...

Thanks for the head start. I'll use what you have provided as a starting point. Thanks again for taking the time to look at this. It is greatly appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

k3yn0t3
  • Question Question
IF() formula that checks if member data meets filtering criteria
Replies
2
Views
484
k3yn0t3
k3yn0t3
S
  • Solved
Need help modifying an Excel VBA coded to add a check for new hires less that 90 days.
Replies
1
Views
508
spittingfire
S
H
  • Solved
compare two workbooks and add relevant data to one workbook
Replies
6
Views
651
Halley yenn
H
S
  • Question Question
Need to Transpose these 1 to 6 columns into one single Column (multiple rows),Keeping the left 2 columns intact. Please help!
2
Replies
11
Views
998
Suman Mehta
S
S
  • Question Question
Change layout of data based on conditions
Replies
8
Views
698
Fluff
Fluff

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,652
Latest member
eduedu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top