Menu

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

Report
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
 -
 mm -
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

Answer 1 / 10
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
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
1
Thank you

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

CCM 5422 users have said thank you to us this month

Answer 2 / 10
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
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
1
Thank you

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

CCM 5422 users have said thank you to us this month

Answer 3 / 10
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
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
Answer 4 / 10
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
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.
Answer 5 / 10
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
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
Answer 6 / 10
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
how can i upload to the forums?
Answer 7 / 10
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
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 :)
Answer 8 / 10
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 789
why should it be copied in row no. 50 and not in any other row.
Answer 9 / 10
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
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.
Answer 10 / 10
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
you are my hero :)

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