Index Match
On 2/1/2018 4:13 PM, Claus Busch wrote:
Hi,
Am Thu, 1 Feb 2018 09:03:44 -0600 schrieb leonard hofstatder:
I think Index Match is what I want but not getting results I want: (and
that's why I'm here asking, you all are the best :-)
Sheet 1 - table 1 - criteria E5:G176
Col E Col G
MWFS TSBGE
MWFS TSBGK
MWF PURNC
MF NPUOT
WS SMPHP
M SCCS
M WESTVS
W MUGMC
F STAHM
Sheet 2 - return results E5:E176
I need to match all cells that contain M(onday) in column E on sheet1
and return the customer code that is in column G sheet1
Sheet 3 will be for W(ednesday)
Sheet 4 will be for F(riday)
Sheet 5 will be for S(aturday)
try it with VBA:
Sub Test()
Dim myRng As Range, c As Range
Dim varCheck As Variant
Dim FirstAddress As String
Dim i As Integer, n As Integer
varCheck = Array("M", "W", "F", "S")
Set myRng = Sheets("Sheet1").Range("E5:E176")
For i = LBound(varCheck) To UBound(varCheck)
n = 5
Set c = myRng.Find(varCheck(i), LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Sheets("Sheet" & i + 2).Cells(n, "E") = _
c.Offset(, 2)
n = n + 1
Set c = myRng.FindNext(c)
Loop While c.Address < FirstAddress
End If
Next
End Sub
Regards
Claus B.
Thanks, Claus - using starter 2010, VBA is not available. I made a
workaround by just making another table and just shading the cells for
each corresponding day. It serves my purpose.
|