I receive daily production reports for the manufacturing of our products that
have to go through 3 manufacturing stations. I enter the information into a
spreadsheet in the following format. The 'X' indicates that the part number
went through the manufacturing station on that day.

Date Part # Stn A Stn B Stn C
6/23 10506 X
6/23 10602 X X
6/23 20506 X
6/23 30904 X

6/24 10506 X X
6/24 10602 X
6/24 30904 X
6/24 10805 X

etc....

I would like to summarize the data on a separate worksheet as follows:

Part # Stn A Stn B Stn C
10506 6/23 6/24 6/24
10602 6/23 6/23 6/24
20506 6/23
30904 6/23 6/24
10805 6/24

etc....

How can this summary table be created? Can it change dynamically as more
data is added to the daily table?

TIA
David

One formulas play that delivers the exact results sought ..

Illustrated in this sample:
http://www.savefile.com/files/1634547
Dynamic Extract Uniques n Corresp Dates.xls

Source data as posted is assumed in sheet: x,
cols A to D, data from row2 down

In another sheet: y,
In A2:
=IF(x!B2="","",IF(COUNTIF(x!B$2:B2,x!B2)1,"",ROWS ($1:1)))
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!B:B,SMALL(A:A ,ROWS($1:1))+1))

In C2**, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(ISNA(MATCH(1,(x!$B$2:$B$10=$B2)*(x!C$2:C$10="X "),0)),"",
INDEX(x!$A$2:$A$10,MATCH(1,(x!$B$2:$B$10=$B2)*(x!C $2:C$10="X"),0)))

Format C2 as date to taste, copy C2 to E2. Select A2:E2, fill down to cover
the max expected extent of data in x. This will dynamically return the exact
results that you seek. Col B returns the list of unique Part#s while cols C
to E returns the corresponding dates. **Adapt the ranges in C2 to suit the
actual extents of your source data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Compass Rose" wrote:
I receive daily production reports for the manufacturing of our products that
have to go through 3 manufacturing stations. I enter the information into a
spreadsheet in the following format. The 'X' indicates that the part number
went through the manufacturing station on that day.

Date Part # Stn A Stn B Stn C
6/23 10506 X
6/23 10602 X X
6/23 20506 X
6/23 30904 X

6/24 10506 X X
6/24 10602 X
6/24 30904 X
6/24 10805 X

etc....

I would like to summarize the data on a separate worksheet as follows:

Part # Stn A Stn B Stn C
10506 6/23 6/24 6/24
10602 6/23 6/23 6/24
20506 6/23
30904 6/23 6/24
10805 6/24

etc....

How can this summary table be created? Can it change dynamically as more
data is added to the daily table?

TIA
David

Hi Max

I'm intrigued by your solution.

For the purpose of the simplicity of the original post, I stated that my
data resides in columns A to E. In fact, in the daily record keeping, the
date is in column AD, the part number is in column X, and the confimations of
what stations the part went through on the particular date are in columns L,
M and N. The actual character that I place in columns L, M and N is a "P",
which when formatted in Wingdings 2, displays as a check mark. The number of
data records that I will ultimately have in the daily production report will
probably reach 5,000 by the time the project is finished, with approximately
3,300 unique part numbers. Since I don't understand the formulas that you
have used, I hesitate to make any changes to reflect the true column
locations of the data I'm trying to summarize.

Would you be kind enough to repost the formulas with the column references
corrected according to the true column locations of the data?

TIA
David

"Max" wrote:

One formulas play that delivers the exact results sought ..

Illustrated in this sample:
http://www.savefile.com/files/1634547
Dynamic Extract Uniques n Corresp Dates.xls

Source data as posted is assumed in sheet: x,
cols A to D, data from row2 down

In another sheet: y,
In A2:
=IF(x!B2="","",IF(COUNTIF(x!B$2:B2,x!B2)1,"",ROWS ($1:1)))
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!B:B,SMALL(A:A ,ROWS($1:1))+1))

In C2**, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(ISNA(MATCH(1,(x!$B$2:$B$10=$B2)*(x!C$2:C$10="X "),0)),"",
INDEX(x!$A$2:$A$10,MATCH(1,(x!$B$2:$B$10=$B2)*(x!C $2:C$10="X"),0)))

Format C2 as date to taste, copy C2 to E2. Select A2:E2, fill down to cover
the max expected extent of data in x. This will dynamically return the exact
results that you seek. Col B returns the list of unique Part#s while cols C
to E returns the corresponding dates. **Adapt the ranges in C2 to suit the
actual extents of your source data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Compass Rose" wrote:
I receive daily production reports for the manufacturing of our products that
have to go through 3 manufacturing stations. I enter the information into a
spreadsheet in the following format. The 'X' indicates that the part number
went through the manufacturing station on that day.

Date Part # Stn A Stn B Stn C
6/23 10506 X
6/23 10602 X X
6/23 20506 X
6/23 30904 X

6/24 10506 X X
6/24 10602 X
6/24 30904 X
6/24 10805 X

etc....

I would like to summarize the data on a separate worksheet as follows:

Part # Stn A Stn B Stn C
10506 6/23 6/24 6/24
10602 6/23 6/23 6/24
20506 6/23
30904 6/23 6/24
10805 6/24

etc....

How can this summary table be created? Can it change dynamically as more
data is added to the daily table?

TIA
David

David,

Here's the earlier sample revised to suit (construct described below):
http://www.freefilehosting.net/download/3j4ff
Dynamic_Extract_Uniques_n_Corresp_Dates_2.xls

Btw, pl take a moment to press the "Yes" button below
from where you're reading this

Construct:
Source data assumed in sheet: x, as per your clarification, data from row2
down
In another sheet: y,
In A2:
=IF(x!X2="","",IF(COUNTIF(x!X$2:X2,x!X2)1,"",ROWS ($1:1)))
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!X:X,SMALL(A:A ,ROWS($1:1))+1))

In C2**, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(ISNA(MATCH(1,(x!$X$2:$X$10=$B2)*(x!L$2:L$10="P "),0)),"",INDEX(x!$AD$2:$AD$10,MATCH(1,(x!$X$2:$X$ 10=$B2)*(x!L$2:L$10="P"),0)))
Format C2 as date to taste, copy C2 to E2. Select A2:E2, fill down to cover
the max expected extent of data in x. This will dynamically return the exact
results that you seek. Col B returns the list of unique Part#s while cols C
to E returns the corresponding dates.
**Adapt the ranges in C2 to suit the actual extents of your source data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Compass Rose" wrote:
Hi Max

I'm intrigued by your solution.

For the purpose of the simplicity of the original post, I stated that my
data resides in columns A to E. In fact, in the daily record keeping, the
date is in column AD, the part number is in column X, and the confimations of
what stations the part went through on the particular date are in columns L,
M and N. The actual character that I place in columns L, M and N is a "P",
which when formatted in Wingdings 2, displays as a check mark. The number of
data records that I will ultimately have in the daily production report will
probably reach 5,000 by the time the project is finished, with approximately
3,300 unique part numbers. Since I don't understand the formulas that you
have used, I hesitate to make any changes to reflect the true column
locations of the data I'm trying to summarize.

Would you be kind enough to repost the formulas with the column references
corrected according to the true column locations of the data?

TIA
David