If / Lookup / Filter type formula ,..?

[Guest]

Hello..
I need a formula to work like the ‘Filter’ function does.

Text Example : (page1)
A B C D
1 John 10:30 10/12/05 Yes
2 Jack 11:00 05/02/06 Yes
3 John 14:10 30/12/05 Pass
4 John 18:00 15/03/04 No
5 Lea 15:34 11/02/05 Pass
And so on …

I require a formula on page2 to list all the attributes of a specified name
in column ‘A of page1’ similar to what a filter can do.
The name is liable to change (not fixed), so the formula will need to
perform a lookup of cell ‘H1’ for what name to return (in example the name in
‘H1’ is ‘John’)

Formula Return Example : (page2)
A B C D H
1 John 10:30 10/12/05 Yes John
2 John 14:10 30/12/05 Pass
3 John 18:00 15/03/04 No

The formula can’t produce any blank rows or duplicate lines where the
incorrect name maybe (this is the fault of my existing formula),

Nor can I simply get away with using the filter function (unfortunately)
unless someone knows how to return the name specified by the filter in cell
H1 (ie. I manually choose to filter ‘John’ and cell ‘H1’ detects this and
shows ‘John’)

Many thanks for any advice..
Monk.

 

[Guest]

Hi Monk,

Have you tried using a pivot table ,

Not sure if that will work fo r you - depneds if you want to count or total
anything...

wAyne_

 

[flummi]

Not sure how the name gets into H1 but provided it is there and you
want all rows on page 1 with that name you could still use the
autofilter if you do this:

In column E on page one type this in E1: =if(a1=page2!$H$1;1;"") and
copy down as required.

Then turn on the autofilter for column E and select "1" from the list

Hope I got your problem.

Hans

 

[Guest]

If a pivot table wont do for you -- here is something that will work..

you will need to add a couple of coulmns to your data sheet so it will look
like this..
A B C D E F G
1 John1 1 John 10:30 10-Dec-05 yes
1 Jack1 2 Jack 11:00 5-Feb-06 yes
2 John2 3 John 14:10 30-Dec-05 pass
3 John3 4 John 18:00 15-Mar-04 no
1 Lea1 5 Lea 15:34 11-Feb-05 pass

1. Column A you can set using =COUNTIF(D$1,D2,D2)
if you use this and fill down for each row - it will give you the number for
each opccurence.
2 Column B is =CONCATENATE(D2. A2)

Then on Sheet 2 you can place

John 2 3 4 5 6
1 1 John 10:30 12/10 yes
2 3 John 14:10 12/30 pass
3 4 John 18:00 3/15 no
4
5
6
7

Cell A1 is your Filter
Cell A2 .... is a count of lines

Cell A2 - A.... is the column number for your list just makes it easier to
copy the vlookups...

Cell B2 ..... is your vlookup as follows
=IF(ISERROR(VLOOKUP(CONCATENATE($A$1,$A2),Sheet1!$B:$G,B$1,FALSE)),"",VLOOKUP(CONCATENATE($A$1,$A2),Sheet1!$B:$G,B$1,FALSE))
just place this in B3 then fill down and right..


Hope you can make sens out of it...

wAyne_

 

[Guest]

Thanks Guys,

Flummi,
Im preferring your method (as it described) as I have the H1 text entry
covered..

But I cant seem to get to cell to accept the formula (appears to have an
issue with the $H$1 part).. Am I missing a space or colon or something ..?

=if(a1=page2!$H$1;1;"")

Thanks,
J

 

[Guest]

Might be a delimiter issue. Have you tried substituting the semicolons with
commas?

=if(a1=page2!$H$1,1,"")

 

[flummi]

Sorry, you are right.

I'm sitting in Germany and we commonly use semicolons as list
separators.

Lookup your local Windoes settings for "regional and language
settings". Like Kevin says it's probably a comma.

Good luck!

Hans

 

[Guest]

Cool this seems to work Brill..
Thanks Guys for your help

Monk..

 

[Guest]

Hi,

I'm using something similar to this, thanks flummi, however when I update
the item in H1 (using the example below) form a validation list the
autofilter doesn't seem to update so I still see the results of the previous
autofilter.

eg. in H1 the original text is "ART" then the next item is selected from the
list "CON". Obviously the formula below updates the column containing the
formula below as the new selection is made. I was hoping that this
automatically update the autofilter but it does not. Is there anyway to force
the filter to refresh?

Thanks,
Andrew