Filter List

[John Moore]

I need to filter a list of data to be able to view each part and the customer
for each part. The example below shows part 10P2345 appearing 4 times, with a
different customer for each order, I need to see the part with each customer
and other data also. How do I do this without using an Auto Filter?

Part Order No. Serial No. Customer Ship date
10P2345 3553232 7992323 H. Potter 16/09/2005
10P2346 3553233 7992324 R. Malcolm 23/09/2005
10P2347 3553234 7992325 J. Peters 23/09/2005
10P2348 3553235 7992326 M. Henderson 24/09/2005
10P2349 3553236 7992327 M. Watters 23/09/2005
10P2350 3553237 7992328 P. Gormley 20/09/2005
10P2345 3553238 7992329 T. Weston 19/09/2005
10P2346 3553239 7992330 J. Horn 23/10/2005
10P2347 3553240 7992331 S. McDonald 30/09/2005
10P2345 3553241 7992332 R. Royston 01/10/2005
10P2346 3553242 7992333 F. Chang 02/10/2005
10P2347 3553243 7992334 M. Strong 03/10/2005
10P2345 3553244 7992335 P. Anders 04/10/2005
10P2346 3553245 7992336 J. Becker 05/10/2005
10P2347 3553246 7992337 S. Donald 05/10/2005

 

[bj]

how do you want to see it?
do you want them to be together with the others around them?
Sort?
do you want them highlighted so that you can spot them?
Conditional format?
what is it with autofilter that makes you not want to use it, because
autofilter is what I would normally recommend?

 

[John Moore]

I want to see them like this,
Part Order No. Serial No. Customer Ship date
10P2345 3553232 7992323 H. Potter 16/09/2005
10P2345 3553238 7992329 T. Weston 19/09/2005
10P2345 3553241 7992332 R. Royston 01/10/2005
10P2345 3553244 7992335 P. Anders 04/10/2005

 

[Max]

One non-array formulas play ..

Assume the source table is in Sheet1, cols A to E, data from row2 down

Use an empty col, say col G
Put in G2: =IF(A2="","",IF(A2=Sheet2!$A$1,ROW(),""))
Copy down to say, G100 to cover the max expected data
(Leave G1 empty)

In Sheet2
------
Input the "Part" in A1: 10P2345

Copy > paste the same col headers over from Sheet1 into A2:E2

Put in A3:
=IF(ISERROR(SMALL(Sheet1!$G:$G,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$G:$G,
ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Copy across to E3, fill down to E101
(cover the same range as in col G in Sheet1)

Format col E as dates

The filtered results for the "Part" input into A1 will appear,
neatly bunched at the top ..