Excel lookup 2 cells to match in 2 rows. [Solved/Closed]

tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Jan 28, 2011 at 04:21 PM - Latest reply:  mm
- Feb 22, 2018 at 09:54 AM
Hello,

I am using Excel to makes archives of PC issues we have in our compagny

I have Info that i have to write in two cells that are standardize with lists on cells C8 and C9 and then look up if it match the infos in the columns B and D of another worksheet


I would like to have a copy paste of all the rows that match those 2 cells


it could be done with VBA, excel or pivot table i dont mind but i need to return those values for a warning.

does anyone can help me?

thank you.




See more 

11 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 30, 2011 at 10:36 PM
1
Thank you
you cannot upload a file in this forum. But you can upload in
speedyshare.com

remove password and post url

goto
http://www.speedyshare.com/
and follow instructions. Perhaps this may need registration

otherwise if it small just highlight the copy and paste it in the message. .

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1826 users this month

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Feb 3, 2011 at 10:08 PM
1
Thank you
try this macro "test
. macro undo undoes he results of the macro test
I did not understand the second part of it.
if there is problem posts back

Sub test()
Dim x1 As String, x2, r As Range
Dim dtemax As Double, j As Integer, k As Integer
With Worksheets("trouble report")
x1 = .Range("C8")
x2 = .Range("c9")
End With
With Worksheets("archives")
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=4, Criteria1:=x1, field:=2, Criteria1:=x2
r.Cells.SpecialCells(xlCellTypeVisible).Copy
End With
With Worksheets("data")
.Range("A50").PasteSpecial
End With
Worksheets("archives").AutoFilterMode = False
With Worksheets("data")
j = .Range("F50").End(xlDown).Row
dtemax = WorksheetFunction.Max(Range(.Range("F50"), .Cells(j, "F")))
For k = j To 51 Step -1
If .Cells(k, "F") <> dtemax Then Range(.Cells(k, 1), .Cells(k, Columns.Count).End(xlToLeft)).Clear
Next k
End With
End Sub



Sub undo()
Dim j As Integer, k As Integer
With Worksheets("data")
j = .Range("A50").End(xlDown).Row
Range(.Cells(j, "A"), .Cells(j, "A").End(xlToRight).End(xlUp)).Offset(1, 0).Clear
End With
End Sub

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1826 users this month

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 29, 2011 at 09:58 PM
0
Thank you
I am rephrasing the question more to clarify to myself

you have some entries in C8 and C9 insheet1.
you want to find out whether these values are available in columns B and D in sheet2
You have not made it clear whether sheet 1 c8 value may be (or not) available only in column B of the other sheet or in either of the columns. It is also not clear whether these may be available in more than one locations.

I take it C8 value may be available either col. B or col. D.

agan
"I would like to have a copy paste of all the rows that match those 2 cells "

which the source the row of C8 in sheet 1?
what is destination row.

please clarify all these points absolutely clearly giving examples
tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Jan 29, 2011 at 10:22 PM
0
Thank you
C8 may only be availaible in D and C9 only in B

so if i clarify i have 2 pages one named "trouble report" one named Archives

If the data on cell C8 "trouble report" Match on a data on column d of "archives" AND data on cell C9 of "trouble report" match with data on column B same row then copy this row to row 50 of "trouble report" I dont really need to copy more than one row.

but if there is nothing, clear row 50

thank you for your concern if you want, i can send you a shorten version of my workbook.
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 29, 2011 at 11:59 PM
0
Thank you
Please post a very small version of your workbook and explain with reference to one or two examples. It will be easier for reply and give a solutin
tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Jan 30, 2011 at 04:40 PM
0
Thank you
how can i upload to the forums?
tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Jan 31, 2011 at 07:04 AM
0
Thank you
http://www.speedyshare.com/files/26597906/pc_ticket_Generatorhelp_needed_-_Copy.xlsm

there we go
(btw on first page i said to copy on page lookup but i changed this for Data, anyway, i can modify after anyway)

thanks for this :)
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Feb 2, 2011 at 06:01 AM
0
Thank you
why should it be copied in row no. 50 and not in any other row.
tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Feb 2, 2011 at 05:01 PM
0
Thank you
my page data has alot of refence for other cell and its the first one free,

the sheet you see there is only a very small version without any of the function my main project has.

Im not really allowed to share the real work has there is some confidential data in it.

if you think that copying on row # 50 is not possible im more open of creating a data2 sheet than changing all my programming on the actual sheet.

thank you.
tyraxor 6 Posts Friday January 28, 2011Registration date February 4, 2011 Last seen - Feb 4, 2011 at 08:52 AM
0
Thank you
you are my hero :)

THANK YOU VERY MUCH!!!!!!