Find or Match by Multiple criterea

Opus · Apr 2, 2010

Can anyone help? Below is a basic way to find rows that match 3 criteria, work out the average to each column G to M of all the rows that match the criteria and copy and past to a repot sheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
How can I find or match to multiple criteria<o

></o

>
<o

></o

>
Private Sub Average()
'
Application.ScreenUpdating = False
Call Openreport
Sheets("Data").Select

'Needs to be variable range
Range("A8:P1413").Select
Range(Selection, Selection.End(xlDown)).Select

'Copy and paste main data for manipulation
Selection.copy
Sheets("AV").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Sort columns by Catagory of Company then by Company Name and then by region scoring
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("E2"), Order2:=xlAscending, _
Key3:=Range("C2"), Order3:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
<o

></o

>
Range(ActiveCell, ActiveCell.Offset(Range(0), 5)).Select
Selection.copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'
'1 copy paste row 2 to row 1 in sheet "AV"
'2 Find first row that matches F1,E1,C1 after row 1
'3 If match found, copy row 2 to sheet "AVE" to the first empty row
'4 delete row 2 in sheet "AV"
'5 loop stage 1
'6 If no match found
'7 copy row 1 in sheet "AVE" (range G1:M1 sum average down on sheet to give total average score)
'to first empty row in sheet "AVER"
'8 delete rows not empty after row 3 in sheet "AVE"
'9 Loop stage 1 until row 2 empty
'

Do Until IsEmpty(ActiveCell)

'Need find all maybe
'Needs to be veriable range
Range("A2:F?").Find(what:=Cells(F1, E1, C1), LookAt:=xlWhole, _
LookIn:=xlValues, _
searchorder:=xlByColumns).Activate
'If Match found Then
ActiveCell.EntireRow.Select
Selection.copy
Sheets("AVE").Select
Do Until IsEmpty(ActiveCell)
Selection.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AV").Select
ActiveCell.EntireRow.Delete
<o

></o

>
'Else
If IsEmpty(ActiveCell) Then
Call AV_Report

Else
Sheets("AVE").Select
Range("A1").Select
ActiveCell.EntireRow.Select
Selection.copy
Sheets("AVER").Select
Do Until IsEmpty(ActiveCell)
Selection.Offset(1, 0).Select
Loop
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("AV").Select
Range("A2").Select
Range(ActiveCell, ActiveCell.Offset(Range(0), 5)).Select
Selection.copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
'End If
Loop

Call Closereport
Application.ScreenUpdating = True
End Sub<o

></o

>
<TABLE class=MsoNormalTable style="WIDTH: 15in; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=1440 border=0><TBODY><TR style="HEIGHT: 25.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 83pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=111>

1<o

></o

>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131>.=B3<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 94pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=125>.=C3<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=107>.=D3<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 128pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171>.=E3<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 104pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=139>.=F3<o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #969696 1pt solid; WIDTH: 62pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid #969696 .5pt" width=83>.=(SUM(G3:G50))/(COUNTIF(G3:G50,">0"))<o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=65> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 44pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=59> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=79> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 50pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=67> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 41pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=55> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 66pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=88> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 188pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=251><<<<<< Average scores >>>>>><o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=64> <o

></o

>
</TD><TD style="BORDER-RIGHT: #969696 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #969696 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #969696 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid #969696 .5pt; mso-border-bottom-alt: solid #969696 .5pt; mso-border-right-alt: solid #969696 .5pt" width=64> <o

></o

>
</TD></TR><TR style="HEIGHT: 38.25pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 83pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" width=111>Date<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=131>Completed By<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 94pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=125>Region<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=107>Department<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 128pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=171>Company<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 104pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=139>Category<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 62pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=83>Admin Accounts<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 49pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=65>Admin Tender Quotes<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 44pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=59>Admin Sales Desk<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 59pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=79>Schedule<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 50pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=67>Service<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 41pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=55>Rep<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 66pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=88>After Care<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 188pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=251>Comments and Issues<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=64>Total<o

></o

>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; BACKGROUND: silver; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 38.25pt; mso-border-right-alt: solid windowtext .5pt" width=64>% Score<o

></o

>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">25/03/2010<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Joe bloggs<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Here<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Commercial<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Time Ltd<o

></o

>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0in; BORDER-TOP: #ece9d8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; PADDING-TOP: 0in; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Clock Makers<o

></o