Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
To filter for 100 documents out of 1000
Hi,
I have a long list of entries (about 1000) and out of these, I need to filter out 100 selected documents. Right now i'm using advanced filter. But in order to do this, I have to manually place all these documents diagonally so as to do it as a OR condition, ie, Document Document Document 11111111 22222222 33333333 It's very time consuming coz i have to manually pull down those document numbers into different rows. Is there a faster way to do this? Regards, Val |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
To filter for 100 documents out of 1000
Hi Val
Since you are dealing with a single field, Document, then the list required can all be entered one below each other in a single column. This gives an OR function Document 11111 22222 33333 Putting items adjacent gives an AND function Document Document 11111 22222 which would be impossible for this field, because it cannot be both 11111 AND 22222 at the same time but Document Document 11111 22222 does give the equivalent of an OR because other entries on the same row are blank Document Document =11111 <=22222 would give all documents in the range between the 2 values. -- Regards Roger Govier "Dolphinv4" wrote in message ... Hi, I have a long list of entries (about 1000) and out of these, I need to filter out 100 selected documents. Right now i'm using advanced filter. But in order to do this, I have to manually place all these documents diagonally so as to do it as a OR condition, ie, Document Document Document 11111111 22222222 33333333 It's very time consuming coz i have to manually pull down those document numbers into different rows. Is there a faster way to do this? Regards, Val |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
To filter for 100 documents out of 1000
Please tell me more or send me your file. I think you can do it with Pivot
Table Report and some customization of it. My e-Mail is : . in subject please wite Ashkan. "Dolphinv4" wrote: Hi, I have a long list of entries (about 1000) and out of these, I need to filter out 100 selected documents. Right now i'm using advanced filter. But in order to do this, I have to manually place all these documents diagonally so as to do it as a OR condition, ie, Document Document Document 11111111 22222222 33333333 It's very time consuming coz i have to manually pull down those document numbers into different rows. Is there a faster way to do this? Regards, Val |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
To filter for 100 documents out of 1000
One play to quickly & dynamically dredge it out on another sheet ..
Assuming source table in Sheet1, cols A to C (say), data from row2 down, where the key col is col A = doc # In Sheet2, Simply enter / paste the entire list of 100 selected doc #s into A1 down Put in B2: =IF(Sheet1!A2="","",IF(ISNUMBER(MATCH(Sheet1!A2,A: A,0)),ROW(),"")) (Leave B1 blank) Paste the same col headers into C1:E1 Put in C2: =IF(ROW(A1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL ($B:$B,ROW(A1)))) Copy C2 to E2. Select B2:E2, fill down to cover the max expected extent of data in Sheet1, eg down to E1100. Hide away col B. Cols C to E will return the required lines from Sheet1, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dolphinv4" wrote: Hi, I have a long list of entries (about 1000) and out of these, I need to filter out 100 selected documents. Right now i'm using advanced filter. But in order to do this, I have to manually place all these documents diagonally so as to do it as a OR condition, ie, Document Document Document 11111111 22222222 33333333 It's very time consuming coz i have to manually pull down those document numbers into different rows. Is there a faster way to do this? Regards, Val |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How change maximal value in excel for filter (1000) ? | New Users to Excel | |||
Excel Documents Don't Appear in My Recent Documents | Excel Discussion (Misc queries) | |||
Why does excel not see more than 1000 lines in filter mode? | Excel Discussion (Misc queries) | |||
Function Help | Excel Worksheet Functions | |||
Amortization Schedule | Excel Worksheet Functions |