PDA

View Full Version : Solved: Filtering



vzachin
08-01-2006, 06:53 PM
I have a data sheet with part numbers and mfg codes that i am downloading from a main frame database. the data contains 2 columns and will have at least 1 row, no more that 10000.
this is a 2-fold question.
the data in column A are 11 characters long.
i need to find all the part numbers with a "K" in the 9th position and a "0","3","4" or "6" in the 10th position. i want to move this data to the bottom of all my data, separated by a blank row.
the last row of data contains 11 dashes in column A and 4 dashes in column B.
i want to remove these dashes.
the way i handle this now is to put a formula in column H to find the part numbers, sort and then cut & paste to the end.
i started recording a macro but got stuck writing the formula. there must be an easier way.

thanks
vzach

CCkfm2000
08-02-2006, 03:04 AM
try this...

you need to use advance filter

see attached file

:whistle:

vzachin
08-02-2006, 06:10 AM
thanks cckfm2000, but i don't know how to use the advanced filter to specifically filter out what i want, mainly a "0","3","4" or "6" in the 10th position, a "K" in the 9th position. And then place this data in column A, under the unfiltered data

CCkfm2000
08-02-2006, 08:04 AM
ok here is another version which don't use advance filter but a sort routine.

i've attached a file again.

hope this is what you want.... :doh: : pray2:

vzachin
08-02-2006, 11:14 AM
i see where you are going with this. how would i be able to incorporate the formula in column C into the macro to filldown in column C to where the data ends in column A so that i don't have to do this manually? the data in column A will be no more than 10000 rows.

thanks again for your efforts
vzach

CCkfm2000
08-04-2006, 01:52 AM
sorry for not replying to you sooner.
just copy cell c5 down all the way to c10000.
see attached file because i've updated the Formula in cell c5.

vzachin
08-04-2006, 09:39 AM
thanks cckfm2000,

thanks for the update and thanks for the great formula.
all is well now

vzach

CCkfm2000
08-04-2006, 10:01 AM
wow my first solution for help

sorted :thumb

lucas
08-04-2006, 11:05 AM
Nice job CCkfm2000