If you can't get advanced filter to work, let's try something else (remember
to have a backup of your data). Move your criteria list off to the right (so
its not in the way)of your large table. Delete any headers in the worksheet
left over from the copy to range for the advanced filter.
An Example Table For Illustration:
A B C D E
Helper Names Amount Names
Jeff 5 Joe
Morgan 10 Morgan
Bob 15
Joe 20
Insert a column in Column A (just to the left of your large table) and label
it something like Helper. In this column, we will match the names from your
data table to the names in your criterial column. So in cell A2 I would
enter:
=IF(ISERROR(MATCH(B2,E$2:E$3,0)),0,MATCH(B2,E$2:E$ 3,0))
you will need to change E$2:E$3 to whatever range your criteria column is
(make sure $ is in your formula).
Copy this formula all the way down column A. If no match is found, 0 s/b
returned, otherwise a number greater than zero s/b returned.
Then go to cell A1 and turn on the autofilter (Data/Filter/Autofilter).
Click the button in column a, select custom from the drop down list and
select values greater than 0.
If necessary, you can copy this filtered list to a new sheet.
Bear in mind that if your names don't match exactly, it could be due to
leading/trailing spaces. Search help for the TRIM function to clean up the
name columns if you need to before filtering.
Good Luck!
"alisonmacd" wrote:
Ok, managed to concatenate names and have put small under large so I can
see everything together.
However can not get Excel to only take those rows that have a Name
match in the smaller.
For the advanced filter I've tried to tell Excel that the list range is
A1:I34500 (I'm assuming the whole of the large one needs to be covered
here), the list criteria is G34505:G34750 (the column that has the now
concatenated Names) and to copy the range to A34755 (blank space under
both). When I try this though it basically replicates the large sheet
rather than only pulling out all rows where it's found a match of Name
on the small.
I just want to tell it that if the Name on the large is also a Name in
the small then pull out that row. I have shown what I'm trying to
achieve below, the totals can be done manually if I can't find a way to
get the filter to do it or find a simple way to code it.
Alison.
E.g.
large
Name Number Subject1 Subject2
A,A 1 4 2
A,A 3 2 3
B,B 2 1 2
C,C 1 3 3
C,C 2 2 3
D,D 3 4 1
small
Name
A,A
D,D
medium (filtered large)
Name Number Subject1 Subject2
A,A 1 4 2
A,A 3 2 3
total 6 5
D,D 3 4 1
total 4 1
--
alisonmacd
------------------------------------------------------------------------
alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323
View this thread: http://www.excelforum.com/showthread...hreadid=379283