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)


Hope this clear and provides enough info.
Thank you!

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.
--
Windows10
Office 2016

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.

Hi,

Am Thu, 1 Feb 2018 17:30:10 -0600 schrieb leonard hofstatder:

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.

filter the range E4:G176 by contains "M" for Monday and copy the
filtered list to the expected sheet. Repeat this for all your days.


Regards
Claus B.
--
Windows10
Office 2016