Excel lookup 2 cells to match in 2 rows.

Solved/Closed
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011
- Jan 28, 2011 at 04:21 PM
 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.




10 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 30, 2011 at 10:36 PM
you cannot upload a file in this forum. But you can upload in
speedyshare.com

remove password and post url

goto
https://authentification.site
and follow instructions. Perhaps this may need registration

otherwise if it small just highlight the copy and paste it in the message. .
1
thank you
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Feb 3, 2011 at 10:08 PM
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
1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 29, 2011 at 09:58 PM
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
0
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011

Jan 29, 2011 at 10:22 PM
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.
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jan 29, 2011 at 11:59 PM
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
0
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011

Jan 30, 2011 at 04:40 PM
how can i upload to the forums?
0
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011

Jan 31, 2011 at 07:04 AM
https://authentification.site/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 :)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Feb 2, 2011 at 06:01 AM
why should it be copied in row no. 50 and not in any other row.
0
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011

Feb 2, 2011 at 05:01 PM
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.
0
tyraxor
Posts
6
Registration date
Friday January 28, 2011
Status
Member
Last seen
February 4, 2011

Feb 4, 2011 at 08:52 AM
you are my hero :)

THANK YOU VERY MUCH!!!!!!
0