Combining multiple data records

[Compass Rose]

I posted this to Worksheet Functions and got no response, hence the post here.

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

 

[Don Guillett]

This should do it. Correct for your sheets and ranges and WORDWRAP.

Sub makeUNIQUEList()
Columns("g:k").ClearContents
Range("B1").Copy Range("G1")
Range("B1:B9").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("G1:G2"), Unique:=True
Range("B1:B9").Copy Range("G1")
Application.CutCopyMode = False
ActiveSheet.ShowAllData
'findpartNUM
'End Sub
'uncomment lines to make 2 separate macros
'Sub findpartNUM()
lr = Cells(Rows.Count, "g").End(xlUp).Row
For Each pn In Range("g2:g" & lr)
With Worksheets("sheet33").Range("b1:b" & Cells(Rows.Count,
"b").End(xlUp).Row)
Set C = .Find(pn, LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If UCase(Application.Trim(Cells(C.Row, "C"))) = "X" Then Cells(pn.Row, "H")
= Cells(C.Row, "a")
If UCase(Application.Trim(Cells(C.Row, "D"))) = "X" Then Cells(pn.Row, "I")
= Cells(C.Row, "a")
If UCase(Application.Trim(Cells(C.Row, "E"))) = "X" Then Cells(pn.Row, "J")
= Cells(C.Row, "a")
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
Next pn
End Sub

 

[Compass Rose]

Thanks for your effort, Don. Where do I place this code? Do I right click on
the sheet tab, View Code and paste it into the code wondow? Will the Unique
List automatically update when I enter new daily production data into the
spreadsheet, or do I have to manually run the macro?

David

 

[Don Guillett]

I tested in a REGULAR module with the macro assigned to a shape.

 

[Herbert Seidenberg]

No macro.
Automatic, except Pivot Table refresh.
http://www.savefile.com/files/1633986

 

[Compass Rose]

Thanks, Don. I tweaked your code a bit to suit my spreadsheet layout, used
some test data and it works like a charm. Thanks for your help!

David

 

[Don Guillett]

Glad to help. Send Wild Turkey

 

[Max]

Ah, darned, beaten to it by other responders over here ..

I just responded to your earlier posting in .worksheet.functions
with one pure formulas play that also 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

 

[Compass Rose]

Hi Max

I'm intrigued by your solution.

For the purpose of the simplicity of the original post, I stated the column
locations of my data as residing 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 confirmations 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 have in the daily production
reports will probably reach 5000 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 that reflect the
true column locations of the data.

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]

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

 

[Compass Rose]

Thank you, Max.

Is it possible to sort the resultant summary table by part number? Will I
have to do a Copy and Paste Special... -> Values before I sort it or can I do
the sort with the table the way it is?

Also, I'm assuming I can hide column A?

David

 

[Max]

Thank you, Max. Welcome

Is it possible to sort the resultant summary table by part number? Will I
have to do a Copy and Paste Special... -> Values before I sort it or can I do
the sort with the table the way it is?

If you want the part#'s auto-sorted in ascending order in col B, just
replace the earlier formulas in A2 & B2 with these, then copy A2:B2 down:

In A2:
=IF(x!X2="","",IF(COUNTIF(x!X$2:X2,x!X2)>1,"",x!X2+ROWS($1:1)/10^10))

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

(No change to the array formula in C2)

Also, I'm assuming I can hide column A?

Yes, of course.

 

[Compass Rose]

The sorting change that you made doesn't work, probably because, for the sake
of simplicity of the original post, I simplified the part number. In fact,
the part numbers are 13 characters long and, for example, appear as follows:

17400100000HW
or
106A01000009S
or
508008P-R(R)W

which I assume renders your sort method ineffective.

I should also mention that my data starts in row 10, which meant that I
changed the original B2 formula to

=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!X:X,SMALL(A:A,ROWS($1:1))+9))
changing the +1 to +9 at the end of the formula. I don't see a corresponding
+1 in the revised B2 formula.

Your help on this and time devoted is truly appreciated.

David

 

[Max]

the part numbers are 13 characters long ..
Aha, if these are actually alphanumerics, then no.
The auto-sort earlier works on numbers

I don't see a corresponding +1 in the revised B2 formula.

That's because it's a slightly different approach.