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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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

Glad to help. Send Wild Turkey

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Compass Rose" wrote in
message
...
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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