Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KH_GS
 
Posts: n/a
Default Is there function to check and list data?


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


--
KH_GS
------------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=529077

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lotus123
 
Posts: n/a
Default Is there function to check and list data?


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.


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=529077

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Is there function to check and list data?

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KH_GS
 
Posts: n/a
Default Is there function to check and list data?


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: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=529077

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Is there function to check and list data?

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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
KH_GS
 
Posts: n/a
Default Is there function to check and list data?


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:
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
------------------------------------------------------------------------
KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920
View this thread: http://www.excelforum.com/showthread...hreadid=529077

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Is there function to check and list data?

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.

Harlan Grove Wrote:

....
Copy the filtered range to another, blank range outside the filtered
rows.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Which function to check any value existed withing a list? Eric Excel Discussion (Misc queries) 1 December 31st 05 12:38 AM
Which function to check any value existed withing a list? Eric Excel Worksheet Functions 1 December 31st 05 12:35 AM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 08:03 AM
Function to check list for specific conditions and return an answe tanya Excel Discussion (Misc queries) 2 July 6th 05 11:43 AM
I have a data list of 7,000, what is the easiest way to check for Excel Excel Worksheet Functions 3 March 21st 05 08:07 PM


All times are GMT +1. The time now is 05:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"