simple EXCEL filter function

[raefe77]

I cant find out how to output data (e.g. BIRD) that is within several columns of one Excel datasheet into a new datasheet.

The thing is that BIRD isnt always in one of the 9 columns, so I only want the rows pasted that actually have the word within the row's nine columns.

This is what I think it should be like (similar):

=IF('ALL CUSTOMERS'!J2:R1216>=BIRD;"BIRD FOUND";"")

This should mean that if in my Excel sheet "All customers" within the nine columns (J-R) the word BIRD is found, then the rows with BIRD FOUND should be pasted on my fresh sheet, otherwise blank.

 

[xlbo]

you will need to add a formula to a currently blank column which checks your 9 columns and returns the result you require. You would then use this single column to filter the data and then paste it into your new sheet

You may consider concatenating the results of the 9 columns together and using the FIND function to test the resultant string eg.

=if(iserror(FIND("BIRD",upper(J2&K2&L2&M2&N2&O2&P2&Q2&R2))),"","BIRD FOUND")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[raefe77]

Thanks for your quick reply Geoff. No wonder I didnt find it out! Is there no formular for this standard filter, which surely would be widely used? I mean, it is only the result from a data filter of several cells!? Are you sure there is no standard function in that big list?

I tried your code out, but of course it didnt recognise the 'upper' because I use the german version. What does upper stand for in german, maybe RUNDEN or AUFRUNDEN? (these are used for numbers though, not strings).

The error message is selecting: "BIRD",upper.

I also see you are using 'J2&K2&L2&M2&N2&O2&P2&Q2&R2' for one row of columns. I then have to drag this down through my whole spreadsheet I suppose.

 

[xlbo]

afraid there is no filter that will work with 1 command over multiple columns - remember - excel is a spreadsheet NOT a database

you may filter a SINGLE column for multiple values but not multiple columns for a single value

Not sure what the UPPER formula is in german but it is used to make all characters upper case (ie all CAPS) - this is used to ensure that mixed case data is not missed in string comparisons

You are correct in that you will need to drag this formula down to the bottom of your dataset but that should only take a second or 2.....the other option is to write some VBA code that will loop through your records and tag each line as appropriate but IMO coded looping often takes longer than manually using a filter.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

How about the following:-

=COUNTIF('All Customers'!J$2:R$2,"*BIRD*")>0

copied down as per Geoff's suggestion and then filter on TRUE.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[raefe77]

Do I need the 'upper' phrase within? Actually this is just an example another word is used (Bird - only first letter in capitals).

This is how far I got but there is still an error:
=WENN(ISTFEHLER(FINDEN("BIRD";('ALL CUSTOMERS'!J2&K2&L2&M2&N2&O2&P2&Q2&R2))),"","Bird found")

 

[raefe77]

Maybe there is just a minor error in my command?

 

[xlbo]

if your data is consistent then you do not necessarily need it. If all your "Birds" are "Bird" as opposed to "BIRD" or "bird" or even "bIRd" then you can just insert the required text and not bother with the UPPER function

Quick question - is this formula on the same sheet as the data ? if so, try:

=WENN(ISTFEHLER(FINDEN("Bird";J2&K2&L2&M2&N2&O2&P2&Q2&R2)),"","Bird found")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[raefe77]

No I wanted the output to be on a new spreadsheet. But for test purpose I entered your command into a cell several rows underneath the last data on the original spreadsheet and this is what I got:

'The formula contains an error' and the last command is highlighted: ,"","Bird found"

If I put a space inbetween the first semi-colons, the error highlighted is the first part after the last two brackets: ,"

Maybe it would be more practical try Ken's proposal if you dont know what causes this error?

 

[xlbo]

ok - the formula should go in the row it is checking NEXT to your dataset - that is the only way you will be able to filter on it to be able to export it to a new sheet

Kens suggestion will also need to be input into the row of data that it is checking to allow the filtering

looks like you use ; as seperators so try:

=WENN(ISTFEHLER(FINDEN("Bird";J2&K2&L2&M2&N2&O2&P2&Q2&R2));"";"Bird found")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Advanced filter could also be used. If your column headings are col1, col2, col3 etc then the criteria should look like this.

col1 col2 col3
*bird*
*bird*
*bird*

If you use rangenames then you can filter to a new location (ie the destination sheet in your workbook)

The ExtractTo range should look like this:
col1 col2 col3

You do not need to use code to achieve this but if you do it would look something like this.

    Range("alldata").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Range("Criteria").CurrentRegion, _
        CopyToRange:=Range("output"), _
        Unique:=False

Gavin

 

[raefe77]

Many thanks Geoff, we found the bird! Your last example worked whilst trying it in the same row. So in future a golden rule, for me, is to ALWAYS put the formula in the SAME ROW at the end (next to the data in subject)?? Otherwise the formula won't work?

Thanks also to Ken and Gavona for your participation, maybe next time i'll get onto you.

 

[xlbo]

for this type of formula - yes but it is more to do with the functionality you want your formula to provide

In this case, you need it next to the data set so that you can filter on it and then move the selected records over to a new sheet. If, however, you are using formulae to create a summary then there is no need for them to be next to the data rows

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Also, depending on your data, you may just want to throw a pivot table at it now that you have your 'Bird Found' column, have the PT report appear on whatever sheet you want and then use that 'Bird Found' column as a page field to filter the data you want.

Just a thought.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[raefe77]

Good thought Geoff. Could you give me an example of how to do that? I've never worked with pivot tables before...

 

[raefe77]

Just going one step back now that we have the solution, would it not have been possible to use:

=IDENTICAL(A1:A7, "Bird")

I suppose it would need adapting a bit, because there were additional words within the cells (e.g. small black bird, bird without feathers, bird on a roof) so it wouldnt be quite identical.

 

[xlbo]

I think you mean the MATCH function in english and yes - you could use

=if(iserror(MATCH("*Bird*",A1:A7,0)),"","Bird Found")

not a huge amount of difference though apart from the initial concatenation

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question