Selecting data that matches certain criteria in one column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Glad I could help and I'll bear that little sticking point in mind for next
time! I learned something too.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select matching records from list 3
Creating Columns from space delimited list 2
How to select one record of each from a column 2
Compare two columns of texts in Excel 2003 7
Search for matches and then append data 1
Count unique records that also meet criteria 3
How can I match numeric data from two column in same worksheet 1
Excel: HLOOKUP Text "approximate" match over 2 sheets problem? 3

Back
Top