Match not sure

[Guest]

I have a spread sheet that has 65 columns with modules (security) and 1500
rows of employees. If an employee has access to a module there is a Y, if
not a N.
How can I pull out everyone that has a Y in all the same columns? I want to
group the employees that have the same module access. I'm not sure if a
match formula will work. I would like to group them in a seperate worksheet.
Thanks,

 

[Bob Phillips]

Why not just use AutoFilter on those columns with a Y value?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Because there are two many columns and I want to pull them out onto another
worksheet without having to copy and paste on each filter.

 

[Bob Phillips]

TRy this then, essentially what I suggested, in code

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim rng As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Columns(2).Insert
.Range("B1").Value = "Temp"
.Range("B2").FormulaR1C1 = "=COUNTIF(RC[1]:RC[" & iLastCol + 1 &
"],""Y"")"
.Range("B2").AutoFill Destination:=.Range("B2").Resize(iLastRow -
1), Type:=xlFillDefault
.Columns("B:B").AutoFilter
.Columns("B:B").AutoFilter Field:=1, Criteria1:="=" & iLastCol - 1,
Operator:=xlAnd
Set rng = .Range("A2").Resize(iLastRow -
1).SpecialCells(xlCellTypeVisible)
rng.Copy Destination:=Worksheets("Sheet3").Range("A1")
.Columns(2).Delete
End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)