Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function to check any value existed withing a list? | Excel Discussion (Misc queries) | |||
Which function to check any value existed withing a list? | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Function to check list for specific conditions and return an answe | Excel Discussion (Misc queries) | |||
I have a data list of 7,000, what is the easiest way to check for | Excel Worksheet Functions |