Solved: Filtering

[vzachin]

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]

try this...

you need to use advance filter

see attached file

[vzachin]

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]

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

[vzachin]

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]

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]

thanks cckfm2000,

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

vzach

[CCkfm2000]

wow my first solution for help

sorted

[lucas]

Nice job CCkfm2000