software

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 quot;applequot;

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

output(in a new column):
apple
red apple
green apple
apple--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: www.excelforum.com/member.php...oamp;userid=32920
View this thread: www.excelforum.com/showthread...hreadid=529077
Presuming your data is in Cells A2:A11; enter the following formula in
cell B2 and copy through B11. Type the word quot;Applequot; into B1.

=IF(ISERROR(FIND($B$1,A2)),quot;quot;,A2)

Basically, this forumla looks to see if it finds the word quot;Applequot; (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.--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: www.excelforum.com/member.php...oamp;userid=28611
View this thread: www.excelforum.com/showthread...hreadid=529077KH_GS wrote...
gt;Can I do a logic check down the rows, and then display the cells that
gt;matches my condition? I want to paste the wanted data in a new column.
gt;Is there any function to do that or do I need a macro?
gt;
gt;Example: to match quot;applequot;
gt;
gt;Data:
gt;apple
gt;red apple
gt;green apple
gt;melon
gt;melon
gt;strawberry
gt;strawberry
gt;apple
gt;melon
gt;strawberry
gt;
gt;output(in a new column):
gt;apple
gt;red apple
gt;green apple
gt;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 gt; Filter
gt; 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.
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.--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: www.excelforum.com/member.php...oamp;userid=32920
View this thread: www.excelforum.com/showthread...hreadid=529077KH_GS wrote...
gt;Actually I would want pineapple to be captured. In fact I left out the
gt;point that I want to match part of the word, i.e using wildcard
gt;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 gt;
Filter gt; 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.
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?

Harlan Grove Wrote:
gt; KH_GS wrote...
gt; gt;Actually I would want pineapple to be captured. In fact I left out
gt; the
gt; gt;point that I want to match part of the word, i.e using wildcard
gt; gt;matching.
gt;
gt; You don't need wildcards. You could use an autofilter, still adding a
gt; column label (or even a blank cell) just above your data, selecting
gt; the
gt; range including the column label and your data and running Data gt;
gt; Filter gt; Autofilter, then click on the dropdown arrow on the right
gt; side
gt; of the column label cell, select (Custom...), and choose 'contains' in
gt; the left entry field and 'apple' in the right entry field and click
gt; OK.
gt; Copy the filtered range to another, blank range outside the filtered
gt; rows.--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: www.excelforum.com/member.php...oamp;userid=32920
View this thread: www.excelforum.com/showthread...hreadid=529077KH_GS wrote...
gt;However if I copy the custom filtered data to a blank column, the rows
gt;do not correspond to the data in adjacent columns. How do I go about
gt;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.

gt;Harlan Grove Wrote:
....
gt;gt;Copy the filtered range to another, blank range outside the filtered
gt;gt;rows.