Menu
Ask a question Report

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

Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
 - - Latest reply:  mm - Feb 22, 2018 at 09:54 AM
Excel Ask a question Report
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 

10 replies

Best answer
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 922
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. .

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 4456 users have said thank you to us this month

Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 922
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

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 4456 users have said thank you to us this month

Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 922
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
Report Respond to tyraxor
Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
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.
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 922
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
Report Respond to tyraxor
Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
0
Thank you
how can i upload to the forums?
Report Respond to tyraxor
Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
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 :)
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 922
0
Thank you
why should it be copied in row no. 50 and not in any other row.
Report Respond to tyraxor
Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
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.
Report Respond to tyraxor
Posts
6
Registration date
Friday January 28, 2011
Last seen
February 4, 2011
0
Thank you
you are my hero :)

THANK YOU VERY MUCH!!!!!!
Ask a question