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