Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

Combining multiple data records
 

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

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

Combining multiple data records
 

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

Combining multiple data records
 

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

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

Combining multiple data records
 

"Compass Rose" wrote:
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(SMA LL(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Combining multiple data records
 

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,SMAL L(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" wrote:

"Compass Rose" wrote:
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(SMA LL(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Combining multiple data records
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Compass Rose" wrote in message
...
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,SMAL L(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