Comparing between 2 worksheets and retreiving data if a match

FUGMAN · #1 02-13-2017, 01:25 PM

I created “data input"worksheet which only contains the data for one record. After data is entered, it will be relocated to another worksheet labeled "location". I would like to compare the data in range E1:G1 (containing first name, mid initial, and last name) on my “data input" worksheet” against the data contained in columns E, F, and G of a multi-row worksheet, labeled "List". If any row of the multi-row worksheet "List" has an identical match in its Columns E, F and G, I would like to import all the remaining data in that row (columns H thru Z) into “the data input sheet”. All the worksheets involved are in the same workbook. Thanking all in advance for any assistance regarding this.

FUGMAN · #2 02-15-2017, 08:09 PM

Since I have not received any responses to this post, I have come up with an alternative approach to resolve my problem. But I still have the same old problem of not having skills to program what I need. Hopefully, someone will respond with a solution for the following requirement.

I have one column of data that I want to Find or Search for a text “xxxxx”. After this text is found, I want to go the NEXT ROW and sent the data in that NEXT ROW cell to cell “A1” on the same worksheet. If there is no data in that NEXT ROW, send text string “not found” to cell “A1”of that same worksheet. Thanks in advance for any help regarding this.

NoSparks · #3 02-16-2017, 08:11 AM

based on post #1, and assuming the first name, initial and last name combination only appears once on the 'List' sheet, you could perhaps use the range.find and .findnext methods based on the last name.

Code:

Sub Fugman_02_16_2017()

Dim Lname As String, Initial As String, Fname As String
Dim fndLastName As Range, firstAddress As String
    
With Sheets("Input Data")
    Lname = .Range("G1").Value
    Initial = .Range("F1").Value
    Fname = .Range("E1").Value
End With

With Sheets("List").Range("G:G")
    Set fndLastName = .Find(What:=Lname, After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
    
    If Not fndLastName Is Nothing Then
        firstAddress = fndLastName.Address
        Do
            If fndLastName.Offset(0, -2).Value = Fname And fndLastName.Offset(0, -1).Value = Initial Then
                fndLastName.Offset(0, 1).Resize(1, 19).Copy Sheets("Input Data").Range("H1")
                Exit Do
            End If
            Set fndLastName = .FindNext(fndLastName)
        Loop While Not fndLastName Is Nothing And fndLastName.Address <> firstAddress
    End If
End With

End Sub

FUGMAN · #4 02-16-2017, 10:57 AM

NoSparks

Thank you for your reply.
I tried the macro with the following results.
fndLastName.Offset(0, 1).Resize(1, 19).Copy Sheets("Input Data").Range("H1")Run time errorSubscript out of range

NoSparks · #5 02-16-2017, 01:06 PM

Change the sheet name to whatever your sheet is named, looks like I got it turned around.
Although I don't know how it got there unless you changed the sheet name (only) at the beginning where it gets the names you're looking for.

FUGMAN · #6 02-16-2017, 05:14 PM

NoSparks

Man !!! You nailed it. Works like a charm.

If you would be so kind, and if you have time, you could really make my day if you could solve my problem as described in my 2nd post of this thread.

Regardless, you are the "Man". Thanks so much for the help you have provided me now and on my previous issue that you solved for me.

NoSparks · #7 02-16-2017, 08:12 PM

Don't know how the second post would be an alternative to the first.

None the less, I think this will do what you indicate you're wanting as long as you're searching for text and not numbers.
Keep your fingers crossed and give it a try.

Code:

Sub Fugman_second_question()

    Dim str As String, ws As Worksheet
    Dim r As Long, col As Long
    
str = InputBox("Enter what to search for", "SEARCH CRITERIA")
If str = "" Then Exit Sub

Set ws = Sheets("Sheet1") '<~~ change to suit
col = 7     '<~~ change to suit ~~~ column 7 is "G"

On Error Resume Next    'if nothing found it errors
r = Application.WorksheetFunction.Match(str, ws.Columns(col), 0)
On Error GoTo 0         'turn error notification back on

If r > 0 Then
    If ws.Cells(r + 1, col) = "" Then
        ws.Range("A1").Value = "not found"
    Else
        ws.Range("A1").Value = ws.Cells(r + 1, col).Value
    End If
Else
    MsgBox "Didn't find  " & str
End If

End Sub

FUGMAN · #8 02-16-2017, 09:53 PM

NoSparks

Thank you once again. Will try this. You are a heck of a good man. Extending yourself to assist an unknown. Just for general info...I am a 74 year old dog trying to learn new tricks.

FUGMAN · #9 02-18-2017, 09:47 AM

NoSparks
Sorry that it has taken me so long to reply, but it has been busy here.
Once again, you delivered in spades. Thank you.
Actually, the second method is a better solution than the first. The data source for my activity is two fold. The second approach was chosen after I discovered the 2nd source. Using the code you provided eliminates a manual intermediate step in my process. This accomplished by using multiple "if-than-else" in your code.

EXAMPLE

str = "Address"
'str = InputBox("Enter what to search for", "SEARCH CRITERIA")
If str = "" Then Exit Sub
Set ws = Sheets("DATA ENTRY") '<~~ change to suit
col = 1 '<~~ change to suit ~~~ column 7 is "G"
On Error Resume Next 'if nothing found it errors
r = Application.WorksheetFunction.Match(str, ws.Columns(col), 0)
On Error GoTo 0 'turn error notification back on

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
If r > 0 Then
If ws.Cells(r + 1, col) = "" Then
ws.Range("I8").Value = "not found"
Else
ws.Range("I8").Value = ws.Cells(r + 1, col).Value
End If
Else
MsgBox "Didn't find " & str
End If
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X
'CITY
If r > 0 Then
If ws.Cells(r + 2, col) = "" Then
ws.Range("I9").Value = "not found"
Else
ws.Range("I9").Value = ws.Cells(r + 2, col).Value
End If
Else
MsgBox "Didn't find " & str
End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X
'STATE
If r > 0 Then
If ws.Cells(r + 3, col) = "" Then
ws.Range("I11").Value = "not found"
Else
ws.Range("I11").Value = ws.Cells(r + 3, col).Value
End If
Else
MsgBox "Didn't find " & str
End If

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX X
'ZIPCODE
If r > 0 Then
If ws.Cells(r + 4, col) = "" Then
ws.Range("I12").Value = "not found"
Else
ws.Range("I12").Value = ws.Cells(r + 4, col).Value
End If
Else
MsgBox "Didn't find " & str
End If

Thanks again.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Index Match Function across different worksheets	shay_mt	Excel	2	04-27-2015 06:04 AM
comparing data	sjp1966	Excel	6	07-23-2014 11:43 AM
Comparing two excel worksheets	SaneMan	Excel	1	06-27-2012 07:52 PM
Comparing Data - MS Excel	ramadevidokkuud	Excel	1	05-19-2011 05:52 AM
Comparing Data	leroytrolley	Excel	1	08-09-2008 08:34 PM