Creating a new sheet from specific data in existing sheet

  • Thread starter Thread starter Rosscoe
  • Start date Start date
R

Rosscoe

I would like to create a new sheet based on specific data from an existing
sheet using key text from cells in a column. I need to search a column range
and if the key text appears then I want to copy that whole row of information
into a new sheet.
For instance, if one cell, or several cells, in column range, say J2:J455,
contain the text "ABCD" then I would like to take that whole row of data, say
A5:M5, and copy it into a new sheet. What formulas do I need to contruct to
do that or does a 'macro' need to be written to do it?
 
You can use a macro, but you don't need to.

You can use AutoFilter instead. Click on the column and:

Data > Filter > AutoFilter > Contains > ABCD

this will hide the rows that don't contain ABCD in the desired column. Copy
the visible rows and paste eslewhere.
 
Are you looking for that specific entry e.g. ABCD or would ABCD be included
within other text in the cell?
 
Actually, this is a built-in feature of XL. Create a new sheet. In A1, type
the name of your header of column J, and in A2, input the criteria you want
found (ABCD).

Now, while still on this new sheet, go to Data - Filter - AdvancedFilter.
Change first option to "Copy to another location". For List range, select the
entire range of data you want to check (all columns) from the data sheet. For
Criteria range, select cells A1:A2. For Copy to, select cell A4. Ok out.

Note that the criteria change is very flexible. If you want a type of "and"
criteria, place the header name and criteria in another column. If you want
an "or" criteria (lets say search column J for XYZ and ABCD) simply place
that in the row below (cell A3).
 
Back
Top