Look Up 2 Values, Match 2 Values Different Tab Return 3rd Value

M

manford7

Joined
Feb 19, 2011
Messages
1
Reaction score
0
Hi, I have a large Movies Excel spreadsheet. To simply, I am only concerned with two separate sheet tabs. The first sheet tab has many columns and is movies I have an interest in, column B is Film Title, Column N is Film Production Year and Column W is American MPAA Certficate Number. A second sheet tab contains a list of some 25,000 movie titles with MPAA Certificate Numbers and Production Year. It is Column A is Production Year, Column B is Movie Title and Column C is MPAA Number. There are duplicate film title names for some of the films in both sheet tabs but each will have a different year, example Deception has been made in 1932, 1946 and 2008 with MPAA Certificate Numbers 4752, 11772 and 43789 respectively and so has 3 entries in the second sheet tab. I have the one entry for this film in the first sheet tab (Deception, 2008) and if I use VLOOKUP it only returns the first MPAA number (4752) as there is no match to the year also.
I believe INDEX and MATCH can be used to look at my Film Title and Year, Match the same in the MPAA Number List and return the correct MPAA Number from the MPAA Number List in Column W of the first sheet tab. I need help to get the formula right.:confused:
It would be great is someone can help me get the formula right.
Thanks.
 
May be some FORMULA can be done. But I find it easier to use a macro. I am sure you are familiar with macro. so I have waritten three macros.
you have TO RUN ONLY "TEST". All the three macros must be parked in one module
the sheet names are sheet1 which contains part of the data, shseet2 full data and sheet3 is copy of original sheet1

The macro "undo" will undo he result of the macro. For this in the beginning itself you should copy sheet1 in sheet 3 and preserve sheet 3 as original data.

NOTE: the three parameters filname, year and MAPP number in sheest1 must be exact atleast in one of the items in sheet2. (I hope what I mean is clear

For you to appreciate I am sending a sample fiel also named "manford" and the macros are in the vbeditor of this file. the macros are also given here.(THERE IS SOME PROBLEM IN UPLOADING MY FILE AS ERROR SAYS INVALID FILE. i AM UPLOADING TO SPPEDYSHARE.COM . THE URL IS

http://www.speedyshare.com/files/26969836/manford.xls

first study the file "manford" and hope its configuration is more or less as your original file. If you are satsified then only use the mcros on your file

(IMPORTANT-KEEP YOUR ORIIGINAL FILE SAFELY SAVED SOMEWHERE)
before testing the macros on them



the macros are


Code: 
Dim r As Range, rfull As Range, rfilt As Range, cf As Range, movie As String
Dim rmov As Range, rmov1 As Range, rmov2 As Range

Sub TEST()
With Worksheets("sheet2")
Set r = Range(.Range("B1"), .Cells(Rows.Count, "B").End(xlUp))
Set rfull = Range(.Range("A1"), .Cells(Rows.Count, "C").End(xlUp))
Set rfilt = .Range("A1").End(xlDown).Offset(5, 0)
r.AdvancedFilter action:=xlFilterCopy, copytorange:=rfilt, unique:=True
Set rfilt = Range(rfilt.Offset(1, 0), rfilt.End(xlDown))
For Each cf In rfilt
movie = cf.Value
rfull.AutoFilter field:=.Range("B1").Column, Criteria1:=movie
Set rmov = .AutoFilter.Range
'msgbox rmov.Address
Set rmov1 = rmov.Offset(1, 0).Resize(rmov.Rows.Count - 1, rmov.Columns.Count)
'msgbox rmov1.Address
Set rmov2 = rmov1.Columns("a:a").SpecialCells(xlCellTypeVisible)
rmov2.Copy Worksheets("sheet1").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0)
Set rmov2 = rmov1.Columns("B:B").SpecialCells(xlCellTypeVisible)
rmov2.Copy Worksheets("sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
Set rmov2 = rmov1.Columns("C:C").SpecialCells(xlCellTypeVisible)
rmov2.Copy Worksheets("sheet1").Cells(Rows.Count, "W").End(xlUp).Offset(1, 0)

rfull.AutoFilter
Next cf

End With
removeduplicates

End Sub
Code: 
Sub removeduplicates()
Dim j As Integer, k As Integer, x
Worksheets("sheet1").Activate
Set r = Range(Range("W1"), Range("w1").End(xlDown))

j = Range("w1").End(xlDown).Row
For k = j To 2 Step -1
x = Cells(k, "W").Value
If WorksheetFunction.CountIf(r, x) > 1 Then
Cells(k, "w").EntireRow.Delete
End If
Next k
ActiveSheet.UsedRange.Select
Selection.Sort key1:=Columns("B"), header:=xlYes
Range("A1").Select
End Sub
Code: 
Sub undo()

Worksheets("sheet1").Cells.Clear
Worksheets("sheet3").Cells.Copy Worksheets("sheet1").Range("A1")

End Sub
 
I need to create macro on excel that will compare two worksheets .
The macro should start with the first worksheet, using the first entry in (Column K) and look for this number in the second worksheet.
If found, the macro should then verify the number from column matches between both worksheets.
If the macro finds the match between the two worksheets, it should highlight or gray out the rows on each spreadsheet.
If a match is not found, the macro should increment down to the next row number in the first worksheet without highlighting or graying out anything
 
am I right that you are different from the original person who posted the first message. if so will it not be better to start a new thread. If you think the problem is similar can you use the macro and explain if there is bug or change you require.
 
Back
Top