Macro required to lookup >1 value in a row and transfer to a seperate ws

[Woody75]

Hi all,

I'm hoping someone can help me...

Obs Sheet

 *CDEFGHIJKLM1SerDateMonth (HIDDEN)Management Check NameEmployee NumberTitleSurnameObservationCorrective Action RequiredDate ClearedHR Action Complete21*********NO32*********NO43*********NO54*********NO65*********NO76*********NO87*********NO98*********NO109*********NO
Spreadsheet FormulasCellFormulaE2=IF(D2="","",MONTH(D2))M2=IF(L2>0,"YES","NO")E3=IF(D3="","",MONTH(D3))M3=IF(L3>0,"YES","NO")E4=IF(D4="","",MONTH(D4))M4=IF(L4>0,"YES","NO")E5=IF(D5="","",MONTH(D5))M5=IF(L5>0,"YES","NO")E6=IF(D6="","",MONTH(D6))M6=IF(L6>0,"YES","NO")E7=IF(D7="","",MONTH(D7))M7=IF(L7>0,"YES","NO")E8=IF(D8="","",MONTH(D8))M8=IF(L8>0,"YES","NO")E9=IF(D9="","",MONTH(D9))M9=IF(L9>0,"YES","NO")E10=IF(D10="","",MONTH(D10))M10=IF(L10>0,"YES","NO") 

Excel tables to the web >>  Excel Jeanie HTML 4

Annex B

 *BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO8Title of Check: ********Personal Details**************9****************************************10Part 1. * Previous Observations Outstanding.***********************11****************************************12MonthSerialObservationAction to be TakenCleared13(a)(b)(c)(d)(e)14*****15*****16*****17*****
Spreadsheet FormulasCellFormulaB14=IF(ISERROR(VLOOKUP($A14,$BH$5:$BT$1504,$BL$1,FALSE))=TRUE,"",VLOOKUP($A14,$BH$5:$BT$1504,$BL$1,FALSE))E14=IF(ISERROR(VLOOKUP($A14,$BH$5:$BT$1504,$BK$1,FALSE))=TRUE,"",VLOOKUP($A14,$BH$5:$BT$1504,$BK$1,FALSE))H14=IF(ISERROR(CONCATENATE(VLOOKUP($A14,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A14,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A14,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A14,$BH$5:$BT$1504,$BQ$1,FALSE)))=TRUE,"",CONCATENATE(VLOOKUP($A14,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A14,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A14,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A14,$BH$5:$BT$1504,$BQ$1,FALSE)))Y14=IF(ISERROR(VLOOKUP($A14,$BH$5:$BT$1504,$BR$1,FALSE))=TRUE,"",VLOOKUP($A14,$BH$5:$BT$1504,$BR$1,FALSE))B15=IF(ISERROR(VLOOKUP($A15,$BH$5:$BT$1504,$BL$1,FALSE))=TRUE,"",VLOOKUP($A15,$BH$5:$BT$1504,$BL$1,FALSE))E15=IF(ISERROR(VLOOKUP($A15,$BH$5:$BT$1504,$BK$1,FALSE))=TRUE,"",VLOOKUP($A15,$BH$5:$BT$1504,$BK$1,FALSE))H15=IF(ISERROR(CONCATENATE(VLOOKUP($A15,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A15,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A15,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A15,$BH$5:$BT$1504,$BQ$1,FALSE)))=TRUE,"",CONCATENATE(VLOOKUP($A15,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A15,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A15,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A15,$BH$5:$BT$1504,$BQ$1,FALSE)))Y15=IF(ISERROR(VLOOKUP($A15,$BH$5:$BT$1504,$BR$1,FALSE))=TRUE,"",VLOOKUP($A15,$BH$5:$BT$1504,$BR$1,FALSE))B16=IF(ISERROR(VLOOKUP($A16,$BH$5:$BT$1504,$BL$1,FALSE))=TRUE,"",VLOOKUP($A16,$BH$5:$BT$1504,$BL$1,FALSE))E16=IF(ISERROR(VLOOKUP($A16,$BH$5:$BT$1504,$BK$1,FALSE))=TRUE,"",VLOOKUP($A16,$BH$5:$BT$1504,$BK$1,FALSE))H16=IF(ISERROR(CONCATENATE(VLOOKUP($A16,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A16,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A16,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A16,$BH$5:$BT$1504,$BQ$1,FALSE)))=TRUE,"",CONCATENATE(VLOOKUP($A16,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A16,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A16,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A16,$BH$5:$BT$1504,$BQ$1,FALSE)))Y16=IF(ISERROR(VLOOKUP($A16,$BH$5:$BT$1504,$BR$1,FALSE))=TRUE,"",VLOOKUP($A16,$BH$5:$BT$1504,$BR$1,FALSE))B17=IF(ISERROR(VLOOKUP($A17,$BH$5:$BT$1504,$BL$1,FALSE))=TRUE,"",VLOOKUP($A17,$BH$5:$BT$1504,$BL$1,FALSE))E17=IF(ISERROR(VLOOKUP($A17,$BH$5:$BT$1504,$BK$1,FALSE))=TRUE,"",VLOOKUP($A17,$BH$5:$BT$1504,$BK$1,FALSE))H17=IF(ISERROR(CONCATENATE(VLOOKUP($A17,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A17,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A17,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A17,$BH$5:$BT$1504,$BQ$1,FALSE)))=TRUE,"",CONCATENATE(VLOOKUP($A17,$BH$5:$BT$1504,$BN$1,FALSE)," ",VLOOKUP($A17,$BH$5:$BT$1504,$BO$1,FALSE)," ",VLOOKUP($A17,$BH$5:$BT$1504,$BP$1,FALSE)," - ",VLOOKUP($A17,$BH$5:$BT$1504,$BQ$1,FALSE)))Y17=IF(ISERROR(VLOOKUP($A17,$BH$5:$BT$1504,$BR$1,FALSE))=TRUE,"",VLOOKUP($A17,$BH$5:$BT$1504,$BR$1,FALSE)) 

Excel tables to the web >>  Excel Jeanie HTML 4

 

[Woody75]

OK so it's official, I can't use excel genie... now you see why i need help...

Does anyone have a macro that can search each row in a ws for 3 types of info (do while/until loop?) and if those criteria are met transfer (incl concatenation) values from that row to the first row of another ws (2nd do while/until loop)?

At the minute i have used VLOOKUP which works incredibly slowly due to the amount of formulas (2 x 2k rows of IF(ISERROR(VLOOKUP.....). I can email a condensed copy of the spreadsheet for you to look at, cause i cannot link it in here.

Many Thanks

Woody

 

[sergioMabres]

sure it does no look very hard to do
but the file or a mock of the file is needed to test the macro
you can copy your file to skydrive/public or dropbox/public and post the public link here
Sergio

 

[Woody75]

I've been trying to work it out on my own, but the line in red gives an error in VBA..

Dim Counter As Integer
Counter = 2


Do Until ThisWorkbook.Sheets("Obs Sheet").Cells(Counter, 2) = ""

If ThisWorkbook.Sheets("Obs Sheet").Range("K" & Counter).Value = "YES" Then

If ThisWorkbook.Sheets("Obs Sheet").Range("D" & Counter).Value = ThisWorkbook.Sheets("Annex B").Range("J8:AA8") Then

If ThisWorkbook.Sheets("Obs Sheet").Range("C" & Counter).Value <> (=MONTH(TODAY())) Then

End If
End If
End If



Loop
End Sub

 

[Woody75]

please can an admin close this thread, i'll re-post the code once i've mulled my way through it

 

[sergioMabres]

Dim myDate As Date
myDate = Date
...
If ThisWorkbook.Sheets("Obs Sheet").Range("C" & counter).Value <> myDate Then
...
End If
...

 

[Woody75]

thx Sergio

 

[Woody75]

Ok, i've made a code that matches what I want to do but it only loops when all of the criteria are met, can anyone help?

Sub Button1_Click()


Dim Counter As Integer
Dim SysDate As Integer
Counter = 2
SysDate = ThisWorkbook.Sheets("Obs Sheet").Range("U2")


Do Until ThisWorkbook.Sheets("Obs Sheet").Cells(Counter, 4) = ""

If ThisWorkbook.Sheets("Obs Sheet").Range("C" & Counter).Value <> SysDate Then

If ThisWorkbook.Sheets("Obs Sheet").Range("M" & Counter).Value = "YES" Then

If ThisWorkbook.Sheets("Obs Sheet").Range("F" & Counter).Value = ThisWorkbook.Sheets("Annex B").Range("J8") Then

ThisWorkbook.Sheets("Obs Sheet").Range("A" & Counter).Value = Counter - 1

End If
End If
End If

Counter = Counter + 1

Loop
End Sub

 

[Woody75]

Never mind, i've found it...