Search in Column and display in another column

[Per_]

Hello all,

I need some help with this: I got a column with strings, I would like to serach for the string LM in the column and display the strings that contain LM in another column.

Ex;

Column1
TT_LM
GG
YY
RR_LM

So I would like to have this in column2
TT_LM
RR_LM

please help!
Per​

 

[Jubjab]

Try

Sub b()

y = 1
For i = 1 To Range("a65536").End(xlUp).Row
x = ""
x = InStr(Range("a" & i).Value, "LM")
If x > 0 Then
Range("b" & y).Value = Range("a" & i).Value
y = y + 1
End If

Next i

End Sub

 

[XOR LX]

Also posted here: Search in Column and display in another column

Regards

 

[hiker95]

Per_,

Sample raw data:

Excel 2007
	A	B
1	TT_LM
2	GG
3	YY
4	RR_LM
5
Sheet1

After the macro using two arrays in memory:

Excel 2007
	A	B
1	TT_LM	TT_LM
2	GG	RR_LM
3	YY
4	RR_LM
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindLM()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735754-search-column-display-another-column.html
Dim a As Variant, b As Variant
Dim i As Long, ii As Long, n As Long
a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
n = Application.CountIf(Columns(1), "*LM*")
ReDim b(1 To n, 1 To 1)
For i = LBound(a, 1) To UBound(a, 1)
  If InStr(a(i, 1), "LM") > 0 Then
    ii = ii + 1
    b(ii, 1) = a(i, 1)
  End If
Next i
Range("B1").Resize(UBound(b, 1), UBound(b, 2)) = b
Columns.AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindLM macro.

 

[hiker95]

Per_,

A message to forum cross posters, please read the following link:
http://www.excelguru.ca/node/7

 

[Weazel]

If the last 2 letters in the cell is always LM


Excel 2010

	A	B
2	TT_LM	TT_LM
3	GG	RR_LM
4	YY
5	RR_LM

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B2 {=IFERROR(INDEX($A$2:$A$5,SMALL(IF(RIGHT($A$2:$A$5,2)="LM",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($B$2:B2))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[XOR LX]

Solution offered by Weazel:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(RIGHT($A$2:$A$5,2)="LM",ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($B$2:B2))),"")

Solution posted on other forum:

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(RIGHT($A$1:$A$10,2)="LM",ROW($A$1:$A$10)),ROWS(C$1:C1))-ROW($A$1)+1),"")

Regards

 

[Weazel]

well looking at the other thread ....

if you'd rather not use control shift enter and you have 2010 or above then maybe....

=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/(RIGHT($A$2:$A$5,2)="LM"),ROWS($C$2:C2))),"")

personally I think I'd go with one of the VBA options.

 

[Per_]

Thanks all!!

You all are very helpfull!! Thanks! Now it works

 

[hiker95]

Per_,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.