If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filter List
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
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 "bj" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
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 .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "John Moore" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using advanced filter to search for criteria in a list | Potatosalad2 | General Discussion | 1 | June 8th, 2005 03:08 AM |
Dont see list of field values in the Filter by Form window | lynn atkinson | Using Forms | 0 | January 13th, 2005 11:59 AM |
filter 1 week out of a list dates? | dazanone | Running & Setting Up Queries | 2 | January 8th, 2005 06:37 AM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
How to make list of unique values? | JulieD | Worksheet Functions | 1 | February 26th, 2004 12:25 PM |