Formula to Find Items which Meet Criteria in Mult Columns

[aggiedave05]

OK...I have a list with 50+ columns. I want to go through each column and get a listing of records which meet a criteria. I realize I could make a filter each column individually to get these names and then copy/paste, but hoping to get something much faster.

For example if I want to get stores which meet sales of $10 or more for each period (Columns B, C, D)

A_____________B________C_______D
1 STORE 1_______12_______15_______8
2 STORE 2________6_______9_______10
3 STORE 3_______15_______9_______15

Then in B4 it would say STORE 1, STORE 2
C4 STORE 1
D4 STORE 2 STORE 3

Keep in mind I am doing this with 50+ columns and 700+ rows of data.

THANKS!

 

[mtb'r]

Try this code:

Sub counting()
    Dim cols, i As Integer
    Dim t, cma As String
    
    cols = Application.WorksheetFunction.CountA(Range("1:1"))
    For i = 2 To cols
        For Each cl In Range(Cells(2, i), Cells(2, i).End(xlDown))
            If t <> "" Then
                cma = ", " 'for the first hit, don't use a comma separator
            Else
                cma = ""
            End If
            If cl.Value > 10 Then t = t & cma & Cells(cl.Row, 1)
        Next cl
        Cells(1, i).End(xlDown).Offset(1, 0) = t
        t = ""
    Next i
    
End Sub