I have a rather massive list of entries that I need to analyze. I am looking
to on sheet 2 list all of the operator clock numbers [once only] that run a
given part number from the data in sheet 1. On sheet 1 the Employee #'s are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column C,
same rows. There are multiple parts and multiple entries of the same operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1 and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

See this:

http://contextures.com/xladvfilter01.html#FilterUR

--
Biff
Microsoft Excel MVP


"MPI Planner" wrote in message
...
I have a rather massive list of entries that I need to analyze. I am
looking
to on sheet 2 list all of the operator clock numbers [once only] that run
a
given part number from the data in sheet 1. On sheet 1 the Employee #'s
are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column
C,
same rows. There are multiple parts and multiple entries of the same
operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1
and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

Hi,

Use advanced filters. Since you want to filter the data to another sheet,
your active cell should be on the worksheet where you want the output. Then
run advanced filters. Also, check the box for unique records only.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MPI Planner" wrote in message
...
I have a rather massive list of entries that I need to analyze. I am
looking
to on sheet 2 list all of the operator clock numbers [once only] that run
a
given part number from the data in sheet 1. On sheet 1 the Employee #'s
are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column
C,
same rows. There are multiple parts and multiple entries of the same
operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1
and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

I need a formula so that I can type in a part number on the other worksheet
and generate summary data on all the entries for that part number.

"Ashish Mathur" wrote:

Hi,

Use advanced filters. Since you want to filter the data to another sheet,
your active cell should be on the worksheet where you want the output. Then
run advanced filters. Also, check the box for unique records only.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MPI Planner" wrote in message
...
I have a rather massive list of entries that I need to analyze. I am
looking
to on sheet 2 list all of the operator clock numbers [once only] that run
a
given part number from the data in sheet 1. On sheet 1 the Employee #'s
are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column
C,
same rows. There are multiple parts and multiple entries of the same
operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1
and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

Excel 2007 Advanced Filter
With Event Macro.
http://c0444202.cdn.cloudfiles.racks.../12_25_09.xlsm

If you are high bandwidth, download and examine my DVD database, which
uses a DVD ID number to perform about 15 single lookups, and then uses
advanced filters to grab the actors that were in that film, which is a
"one-to-many' type relationship.

Yours seems quite easy, a slight variant of my lookup code.

You should define a small data set that represents your array in a
micro form. That way, we can know how your rows and columns are laid
out, as your description still leaves questions.
This is a safe workbook. My other workbooks are posted on the Micrsoft
site. They do not take macro enabled though, so this one cannot be
posted there.

_http://www.mediafire.com/?tytzztygiqr



On Fri, 11 Dec 2009 07:06:01 -0800, MPI Planner
wrote:

I need a formula so that I can type in a part number on the other worksheet
and generate summary data on all the entries for that part number.

"Ashish Mathur" wrote:

Hi,

Use advanced filters. Since you want to filter the data to another sheet,
your active cell should be on the worksheet where you want the output. Then
run advanced filters. Also, check the box for unique records only.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MPI Planner" wrote in message
...
I have a rather massive list of entries that I need to analyze. I am
looking
to on sheet 2 list all of the operator clock numbers [once only] that run
a
given part number from the data in sheet 1. On sheet 1 the Employee #'s
are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column
C,
same rows. There are multiple parts and multiple entries of the same
operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1
and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

On Tue, 27 Oct 2009 13:45:01 -0700, MPI Planner
wrote:

I have a rather massive list of entries that I need to analyze. I am looking
to on sheet 2 list all of the operator clock numbers [once only] that run a
given part number from the data in sheet 1. On sheet 1 the Employee #'s are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column C,
same rows. There are multiple parts and multiple entries of the same operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1 and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

This is one approach to what you want, but would better be performed on
a listing where you have already parsed out all duplicates.

So, it is almost there.

It uses advanced filters and a few lookups and a macro to operate it.

There is sample data, but your ability to fit it to your sheet should
be no problem.

You must note that I use named ranges for "JobNum" and "Technicians",
which makes cutting and pasting formulas easier and less problematic and
references to data much simpler than specified range calls.

If you could give the actual sheet structures, I could adapt this to
your data to make your utilization of it easier. As it stands, it looks
as if a pivot table query needs to be done, and the listing made from
that table's results, which is where your automation requisite comes in,
as it appears you do not want it to be manually done.

On Sat, 26 Dec 2009 11:48:44 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

On Tue, 27 Oct 2009 13:45:01 -0700, MPI Planner
wrote:

I have a rather massive list of entries that I need to analyze. I am looking
to on sheet 2 list all of the operator clock numbers [once only] that run a
given part number from the data in sheet 1. On sheet 1 the Employee #'s are
listed in Column B rows 1 thru 45,000. The Part ID's are listed in Column C,
same rows. There are multiple parts and multiple entries of the same operator
running the parts. In sheet 2 I am looking to enter a Part ID in cell A1 and
then A2 down would list employee clock numbers
Example

A1: Product 629
A2: 0006
A3: 0124
A4: 0156

Even if operator '0124' ran 'Product 629' 300x's I only need it listed in
this list once.

This is one approach to what you want, but would better be performed on
a listing where you have already parsed out all duplicates.

So, it is almost there.

It uses advanced filters and a few lookups and a macro to operate it.

There is sample data, but your ability to fit it to your sheet should
be no problem.

You must note that I use named ranges for "JobNum" and "Technicians",
which makes cutting and pasting formulas easier and less problematic and
references to data much simpler than specified range calls.

If you could give the actual sheet structures, I could adapt this to
your data to make your utilization of it easier. As it stands, it looks
as if a pivot table query needs to be done, and the listing made from
that table's results, which is where your automation requisite comes in,
as it appears you do not want it to be manually done.

Would have been nice if I had actually posted the link:

_http://www.mediafire.com/?womfufwqdgy

On Fri, 25 Dec 2009 12:44:10 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007 Advanced Filter
With Event Macro.
http://c0444202.cdn.cloudfiles.racks.../12_25_09.xlsm


Nice work. I thought that what he wanted was to put in a part and see
all employees that worked on that part, and strip away repeats as the
table appears to contain all instances of work performed, and he merely
wants a "Who worked on this part" query, which is a single listing of
employees.

I did a similar listing, but did not strip duplicates, so mine is
incomplete.

Your filter code looks better than mine, but I think they are both from
you, actually.

Is this the improved script? :-)