We help IT Professionals succeed at work.

finding items in a column

zachvaldez
zachvaldez asked
on
I have 3 columns and I need to find all in column A that starts with GBH and list I col D
Comment
Watch Question

Author

Commented:
oh  column A pls edit
Commented:
=IF(LEFT(A1,3)="GBH", A1, "").  

Put this formula in D1 and copy down.  Assuming data in column A starts in row 1.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
If you want to use VBA then try this.

Sub FindGBH()
Dim lngLastRow As Long
Dim cel As Range
Dim lngNR As Long

lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lngNR = 1
Columns("A").AutoFilter
ActiveSheet.Range("$A$3:$A$7").AutoFilter Field:=1, Criteria1:="=GBH*", _
    Operator:=xlAnd
For Each cel In Columns("A").Cells.SpecialCells(xlCellTypeVisible)
    Range("D" & lngNR) = cel
    lngNR = lngNR + 1
Next
Columns("A").AutoFilter
End Sub

Open in new window

Author

Commented:
that worked but now I want to removed all does not show in col d

Author

Commented:
how to count all in Col D that has TKO begins
Commented:
Here is one way...

Parse the TKO

Count TKO's

See example
EE.xlsx
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
To remove all that doesn't show in column 'D'.

Sub FindGBH()
Dim lngLastRow As Long
Dim cel As Range
Dim colFound As New Collection
Dim lngEntry As Long

lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

Columns("A").AutoFilter
ActiveSheet.Range("$A$1:$A$7").AutoFilter Field:=1, Criteria1:="=GBH*", _
    Operator:=xlAnd
For Each cel In Columns("A").Cells.SpecialCells(xlCellTypeVisible)
    colFound.Add cel.Text
Next
Columns("A").AutoFilter

Columns("A").Clear
For lngEntry = 1 To colFound.Count
    Cells(lngEntry, "A") = colFound(lngEntry)
    Cells(lngEntry, "D") = colFound(lngEntry)
Next

Application.ScreenUpdating = True

End Sub

Open in new window


If you mean "how to count all in Col A that has TKO begins, then change the "=GBH*" in line 12 to "=TKO*"
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018