How to find common strings of text then copy those rows elsewhere?

[energyzone]

I have a spreadsheet with text data on over 500 lines (rows). I'm wanting to
find all those rows that have common text, ex.-"ABC Hospital", and move or
copy those rows to a different section or worksheet. Is there a way to do
this at one time rather than copy/pasting individual rows?

 

[Mike H]

Hi,

Your question is a bit thin on detail but this may help. Alt +f11 to open VB
editor, double click 'This workbook' and paste it in and run it.

It looks for your text string in the used range of column A and if it finds
copies the entirerow and pastes it into sheet 2

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select 'Change to suit
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = "ABC Hospital" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Copy
End If

Sheets("Sheet2").Select 'Change to where you want it
Range("A1").Select 'Change to where you want it
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Mike

 

[Ashish Mathur]

Hi,

Another way to do it is the following:

1. Assuming your list starts from A2 downwards;
2. Enter the following formula in cell B2 and copy downwards
=IF(ISNUMBER(FIND("abc",A2)),MAX($B$1:B1)+1,""). Please replace the text in
quotes with the text you are looking for;
3. Now you will notice that wherever there is "abc" or your desired text in
the cell, it will have a number in column B'
4. Now in a seperate cell (say C2), just enter 1 and in the next cell down
below, do =C2+1 and copy downwads. You will now have all consecutive
natural numbers;
5. Use the INDEX() function in cell D2 and down below to get the desired
result.

Do let me know if you need any other help.

 

[energyzone]

Thanks! Unfortunately I know nothing about macros. When I click on This
Workbook, all my rows are displayed. Does it matter where I copy the material
you gave me?

 

[Mike H]

As I said in my original response,

ALT+F11 to open the VB editor. Double click 'this workbook' which is on the
left side and then paste the code in on the right hand side. Because you go
no detail in your original post you will have to change the name of the sheet
to search (Curently Sheet1) and the sheet to paste to (Currently sheet2)

In the 2 lines above you will need to change the A (£ instances) to the
actual column to search.

to run the code click
Tools|Macro|Macros.
Select the name of this macro (Copyit) and click run.

If your unesay with this then another respondent has given you a worksheet
based solution.

Mike

 

[Gord Dibben]

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook(project) and right-click>Insert>Module.

Paste the code into that general module.

Do not paste it into the Thisworkbook module as Mike suggests. Thisworkbook
module should be reserved for event code, not general macros.

As an alternative.........have you looked at Data>Filter>Autofilter?


Gord Dibben MS Excel MVP

 

[Mike H]

Well spotted thanks for the correction

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open the Project Explorer.

Select your workbook(project) and right-click>Insert>Module.

Paste the code into that general module.

Do not paste it into the Thisworkbook module as Mike suggests. Thisworkbook
module should be reserved for event code, not general macros.

As an alternative.........have you looked at Data>Filter>Autofilter?


Gord Dibben MS Excel MVP