Lookup and Display Multiple Unique values based on criteria

M

MPI Planner

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.
 
A

Ashish Mathur

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
 
M

MPI Planner

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 said:
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 said:
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.
 
C

CellShocked

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



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 said:
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 said:
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.
 
C

CellShocked

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.
 
C

CellShocked



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? :)
 

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

Top