I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

No need to do that. You can keep the data on separate sheets if you wish.
It might be easier for you to do that but it doesn't really matter.

Example:

If your 8000 rows are on sheet 1 and your job titles list (728) are on sheet
2. Highlight the entire range of job titles and click Insert--Name--Define.

Type the name MyTitles in the field for it. Make sure that the range is
accurate in the "Refers To" field and then click Add. This creates your
named range. You can then use this name in a formula.

Go back to your main sheet with the 8000 records and insert three blank
lines above your data (This tends to be the easiest method).

Leave the first cell in the first row blank.

In the first cell of the second row, enter the formula. For the cell
refernce (currently G2 in the formula) use the first cell containing data in
the column you wish to search. So if on your main sheet your job titles are
in column B and the first one (not the header) is in row 8 then use B8 in the
formula.

Select a cell within the 8000 rows of data and choose
Data--Filter--Advanced Filter. The "List Range" should cover your 8000
rows, make sure it does.

Click into the "Criteria" field and then highlight both the cell containing
the formula AND the blank cell above it.

If you wish to paste the resultant list elsewhere (perhaps on another sheet)
then select the option and choose where you wish to paste it. Then click OK.

Sounds complicated but it really isn't. Any problems, feel free to shout
again :)

DC


"Anthony" wrote:

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

sorry to bug you again but it seems to be only leaving me withe the header
row??

"Dom_Ciccone" wrote:

No need to do that. You can keep the data on separate sheets if you wish.
It might be easier for you to do that but it doesn't really matter.

Example:

If your 8000 rows are on sheet 1 and your job titles list (728) are on sheet
2. Highlight the entire range of job titles and click Insert--Name--Define.

Type the name MyTitles in the field for it. Make sure that the range is
accurate in the "Refers To" field and then click Add. This creates your
named range. You can then use this name in a formula.

Go back to your main sheet with the 8000 records and insert three blank
lines above your data (This tends to be the easiest method).

Leave the first cell in the first row blank.

In the first cell of the second row, enter the formula. For the cell
refernce (currently G2 in the formula) use the first cell containing data in
the column you wish to search. So if on your main sheet your job titles are
in column B and the first one (not the header) is in row 8 then use B8 in the
formula.

Select a cell within the 8000 rows of data and choose
Data--Filter--Advanced Filter. The "List Range" should cover your 8000
rows, make sure it does.

Click into the "Criteria" field and then highlight both the cell containing
the formula AND the blank cell above it.

If you wish to paste the resultant list elsewhere (perhaps on another sheet)
then select the option and choose where you wish to paste it. Then click OK.

Sounds complicated but it really isn't. Any problems, feel free to shout
again :)

DC


"Anthony" wrote:

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

Not sure why it should be doing that. I've tested it at my end and it should
work. Most likely my explanation wasn't up to the job.

Best advice I can give is to see Debra Dalgleish's site as this contains
useful screenshots that may help:

http://www.contextures.com/xladvfilter01.html

"Anthony" wrote:

sorry to bug you again but it seems to be only leaving me withe the header
row??

"Dom_Ciccone" wrote:

No need to do that. You can keep the data on separate sheets if you wish.
It might be easier for you to do that but it doesn't really matter.

Example:

If your 8000 rows are on sheet 1 and your job titles list (728) are on sheet
2. Highlight the entire range of job titles and click Insert--Name--Define.

Type the name MyTitles in the field for it. Make sure that the range is
accurate in the "Refers To" field and then click Add. This creates your
named range. You can then use this name in a formula.

Go back to your main sheet with the 8000 records and insert three blank
lines above your data (This tends to be the easiest method).

Leave the first cell in the first row blank.

In the first cell of the second row, enter the formula. For the cell
refernce (currently G2 in the formula) use the first cell containing data in
the column you wish to search. So if on your main sheet your job titles are
in column B and the first one (not the header) is in row 8 then use B8 in the
formula.

Select a cell within the 8000 rows of data and choose
Data--Filter--Advanced Filter. The "List Range" should cover your 8000
rows, make sure it does.

Click into the "Criteria" field and then highlight both the cell containing
the formula AND the blank cell above it.

If you wish to paste the resultant list elsewhere (perhaps on another sheet)
then select the option and choose where you wish to paste it. Then click OK.

Sounds complicated but it really isn't. Any problems, feel free to shout
again :)

DC


"Anthony" wrote:

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

have since figured it out. Had the Jobtitles on a separate file rather than a
worksheet on the same file. Duh
Appreciate your help. Have saved me hours!

thank you

"Anthony" wrote:

sorry to bug you again but it seems to be only leaving me withe the header
row??

"Dom_Ciccone" wrote:

No need to do that. You can keep the data on separate sheets if you wish.
It might be easier for you to do that but it doesn't really matter.

Example:

If your 8000 rows are on sheet 1 and your job titles list (728) are on sheet
2. Highlight the entire range of job titles and click Insert--Name--Define.

Type the name MyTitles in the field for it. Make sure that the range is
accurate in the "Refers To" field and then click Add. This creates your
named range. You can then use this name in a formula.

Go back to your main sheet with the 8000 records and insert three blank
lines above your data (This tends to be the easiest method).

Leave the first cell in the first row blank.

In the first cell of the second row, enter the formula. For the cell
refernce (currently G2 in the formula) use the first cell containing data in
the column you wish to search. So if on your main sheet your job titles are
in column B and the first one (not the header) is in row 8 then use B8 in the
formula.

Select a cell within the 8000 rows of data and choose
Data--Filter--Advanced Filter. The "List Range" should cover your 8000
rows, make sure it does.

Click into the "Criteria" field and then highlight both the cell containing
the formula AND the blank cell above it.

If you wish to paste the resultant list elsewhere (perhaps on another sheet)
then select the option and choose where you wish to paste it. Then click OK.

Sounds complicated but it really isn't. Any problems, feel free to shout
again :)

DC


"Anthony" wrote:

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?

Glad I could help and I'll bear that little sticking point in mind for next
time! I learned something too.

"Anthony" wrote:

have since figured it out. Had the Jobtitles on a separate file rather than a
worksheet on the same file. Duh
Appreciate your help. Have saved me hours!

thank you

"Anthony" wrote:

sorry to bug you again but it seems to be only leaving me withe the header
row??

"Dom_Ciccone" wrote:

No need to do that. You can keep the data on separate sheets if you wish.
It might be easier for you to do that but it doesn't really matter.

Example:

If your 8000 rows are on sheet 1 and your job titles list (728) are on sheet
2. Highlight the entire range of job titles and click Insert--Name--Define.

Type the name MyTitles in the field for it. Make sure that the range is
accurate in the "Refers To" field and then click Add. This creates your
named range. You can then use this name in a formula.

Go back to your main sheet with the 8000 records and insert three blank
lines above your data (This tends to be the easiest method).

Leave the first cell in the first row blank.

In the first cell of the second row, enter the formula. For the cell
refernce (currently G2 in the formula) use the first cell containing data in
the column you wish to search. So if on your main sheet your job titles are
in column B and the first one (not the header) is in row 8 then use B8 in the
formula.

Select a cell within the 8000 rows of data and choose
Data--Filter--Advanced Filter. The "List Range" should cover your 8000
rows, make sure it does.

Click into the "Criteria" field and then highlight both the cell containing
the formula AND the blank cell above it.

If you wish to paste the resultant list elsewhere (perhaps on another sheet)
then select the option and choose where you wish to paste it. Then click OK.

Sounds complicated but it really isn't. Any problems, feel free to shout
again :)

DC


"Anthony" wrote:

Thanks for your help her.
So I should cut and paste the Job titles (724) below the coresponding column
in the table with the 8000 records then...... I am not sure how I would do
the rest

"Dom_Ciccone" wrote:

Anthony,

Debra Dalgleish answered a similar post to this before so thanks to her for
this solution.

There are several methods of doing this but probably the best is to use an
Advanced Filter. Name the range that contains the criteria list (the 728
records). For example call it MyTitles.

For the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the list of items, and the column
in the table that you want to check.

For example, if the column to check is column G, and the data starts in
row 2:

=COUNTIF(MyTitles,G2)<0

When you run the Advanced Filter, select both the blank heading cell,
and the cell with the formula, for the criteria range.

This will filter all those records that have a matching job title and hide
everything else. If you need this list separate, just tick the "Copy To
Another Location" option in the Advanced Filter dialog box.

Hope that helps.
DC


"Anthony" wrote:

I have a database of 8000 records. I have a second list of 728 job titles
which I have to extrapolate from the first list ie i only want the records
from the 8000 which match the job titles from the second list. What is the
best way to do this?