This may be simple but I've never tried it in a pc environment.

I have a large worksheet (35000 rows) and a small worksheet. On the
large there is a column, Name, which is in the format 'surname,
forename'. In the smaller one I have one field Surname and a separate
field called Forename. These were not created by me nor the same
person so this is why they aren't consistant.

I'd like to extract rows from the large worksheet where the name is
matched on the smaller one and create a new worksheet. Each Name may
appear many times on the large so the extract could be one row or 20
(for example). I'd then like to do subtotals by person.

I wondered how best to do this in Excel. Would I have to use VBA and
if so would it be fairly straightforward? The smaller worksheet is
quite big but if it'd make coding simpler I could manually change the 2
fields to one.

I've done this sort of thing previously but in the mainframe
environment but don't know where to start with the pc environment.

Help will be appreciated,
Alison.


--
alisonmacd
------------------------------------------------------------------------
alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323
View this thread: http://www.excelforum.com/showthread...hreadid=379283

First, on the smaller worksheet, I'd concatenate the SURNAME and the FORENAME
so that it's in the same format as on the large worksheet.

Surname = A1 - SMITH
Forname = B1 - JOHN

C1 = A1&", "&B1
C1 Result should be SMITH, JOHN

I'd then use the VLOOKUP function to match the concatenated results from the
small worksheet from the large one.

http://www.mvps.org/dmcritchie/excel/vlookup.htm

Come back if you have more questions.

Barb Reinhardt

"alisonmacd" wrote:


This may be simple but I've never tried it in a pc environment.

I have a large worksheet (35000 rows) and a small worksheet. On the
large there is a column, Name, which is in the format 'surname,
forename'. In the smaller one I have one field Surname and a separate
field called Forename. These were not created by me nor the same
person so this is why they aren't consistant.

I'd like to extract rows from the large worksheet where the name is
matched on the smaller one and create a new worksheet. Each Name may
appear many times on the large so the extract could be one row or 20
(for example). I'd then like to do subtotals by person.

I wondered how best to do this in Excel. Would I have to use VBA and
if so would it be fairly straightforward? The smaller worksheet is
quite big but if it'd make coding simpler I could manually change the 2
fields to one.

I've done this sort of thing previously but in the mainframe
environment but don't know where to start with the pc environment.

Help will be appreciated,
Alison.


--
alisonmacd
------------------------------------------------------------------------
alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323
View this thread: http://www.excelforum.com/showthread...hreadid=379283

You would need to concatenate the names as Barb said, but I don't think
VLOOKUP will do what you want as it will only return the first matching value
(you said you had multiple rows for each name)

You could use advanced filter (Tools/Filter/Advanced Filter). For example:


Your Big List
A B
1 Names Amount
2 Blakley, Jeff 5
3 Blakley, Morgan 10
4 Jones, Joel 15
5 Ross, Bob 20
6 Blakley, Jeff 25
7 Jones, Joel 30
8 Ross, Bob 35


Concatenated Names from your small list
11 Names
12 Blakley, Morgan
13 Jones, Joel
14 Ross, Bob


Column Names For the columns you want pulled out.
17 Names Amount
Blakley, Morgan 10
Jones, Joel 15
Ross, Bob 20
Jones, Joel 30
Ross, Bob 35


Click Tools/Filter/Advanced Filter, Copy to new location, List Range =
A1:B8, Criteria Range = A11:A14, Copy To Range = A17:B17

Then move the resulting table to whereever you want. If the list is large,
I would imagine you could move your criteria and Copy To ranges over to the
side of the spreadsheet instead of below (I don't know if everything would
line up in this post if I did that here).

if there are extra (leading / trailing / extras in the middle), you can use
the TRIM function on your data before filtering.



"alisonmacd" wrote:


This may be simple but I've never tried it in a pc environment.

I have a large worksheet (35000 rows) and a small worksheet. On the
large there is a column, Name, which is in the format 'surname,
forename'. In the smaller one I have one field Surname and a separate
field called Forename. These were not created by me nor the same
person so this is why they aren't consistant.

I'd like to extract rows from the large worksheet where the name is
matched on the smaller one and create a new worksheet. Each Name may
appear many times on the large so the extract could be one row or 20
(for example). I'd then like to do subtotals by person.

I wondered how best to do this in Excel. Would I have to use VBA and
if so would it be fairly straightforward? The smaller worksheet is
quite big but if it'd make coding simpler I could manually change the 2
fields to one.

I've done this sort of thing previously but in the mainframe
environment but don't know where to start with the pc environment.

Help will be appreciated,
Alison.


--
alisonmacd
------------------------------------------------------------------------
alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323
View this thread: http://www.excelforum.com/showthread...hreadid=379283

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

Do your list criteria and copy to ranges have headers? They should and you
will need to include the header rows when you specify the range for these in
the advanced filter.



"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

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

it works! or at least the extraction bit does. i now need to do the
subtotals, will do a separate post for that so it's not so confusing.

thank you for your help.

alison.


--
alisonmacd
------------------------------------------------------------------------
alisonmacd's Profile: http://www.excelforum.com/member.php...o&userid=24323
View this thread: http://www.excelforum.com/showthread...hreadid=379283