Is there function to check and list data?

[KH_GS]

Can I do a logic check down the rows, and then display the cells tha
matches my condition? I want to paste the wanted data in a new column
Is there any function to do that or do I need a macro?

Example: to match "apple"

Data:
apple
red apple
green apple
melon
melon
strawberry
strawberry
apple
melon
strawberry

output(in a new column):
apple
red apple
green apple
appl

 

[Lotus123]

Presuming your data is in Cells A2:A11; enter the following formula in
cell B2 and copy through B11. Type the word "Apple" into B1.

=IF(ISERROR(FIND($B$1,A2)),"",A2)

Basically, this forumla looks to see if it finds the word "Apple" (or
other word specified) in the cell to the left. If it finds it, it
returns the cell...if it doesn't, it returns a blank cell.

 

[Harlan Grove]

KH_GS wrote...

Can I do a logic check down the rows, and then display the cells that
matches my condition? I want to paste the wanted data in a new column.
Is there any function to do that or do I need a macro?

Example: to match "apple"

Data:
apple
red apple
green apple
melon
melon
strawberry
strawberry
apple
melon
strawberry

output(in a new column):
apple
red apple
green apple
apple

It's not so simple if you don't want to include pineapple. An advanced
filter may be easiest. Give the original data a column label in the row
above the topmost entry, say, fruit. Enter the following in a 3 row by
1 column range (I'll assume G1:G3).

fruit
apple
* apple

Select the original data range and issue the menu colland Data > Filter

Advanced Filter. In the Advanced Filter dialog, enter G1:G3 as the criterial range and click OK. This will leave only rows containing the matching entries in the original data visible. Copy the filtered data and paste into another range outside the filtered rows (generally safest to paste into a different worhsheet).

You could also enter fruit in yet another cell with blank cells below
it, then in the Advanced Filter dialog, select copy to another location
and set the copy to range to the address of this other cell containing
fruit.

 

[KH_GS]

Actually I would want pineapple to be captured. In fact I left out th
point that I want to match part of the word, i.e using wildcar
matching

 

[Harlan Grove]

KH_GS wrote...

Actually I would want pineapple to be captured. In fact I left out the
point that I want to match part of the word, i.e using wildcard
matching.

You don't need wildcards. You could use an autofilter, still adding a
column label (or even a blank cell) just above your data, selecting the
range including the column label and your data and running Data >
Filter > Autofilter, then click on the dropdown arrow on the right side
of the column label cell, select (Custom...), and choose 'contains' in
the left entry field and 'apple' in the right entry field and click OK.
Copy the filtered range to another, blank range outside the filtered
rows.

 

[KH_GS]

However if I copy the custom filtered data to a blank column, the row
do not correspond to the data in adjacent columns. How do I go abou
that?

 

[Harlan Grove]

KH_GS wrote...

However if I copy the custom filtered data to a blank column, the rows
do not correspond to the data in adjacent columns. How do I go about
that?

More details. I said to copy the filtered data into different *ROWS*,
not necessarily different columns. If your data before filtering were
in A1:A100, then you filtered it, then copied it, you shouldn't try
pasting it into rows 1 through 100 in any other column.

Where's your original data and where did you try copying the filtered
data?

Also, your sample data and problem descriptions before this have only
mentioned a single column of data. Do you have more columns? If so,
you'd need to add them to the filtered range.