Sorting account codes

i was wondering if it is possible to sort the following into number code or maybe hide everything but one number

like below i would want to hide everything but the codes with 7 in them

AA20001
AA20002
AA50001
AA70001
AB20001
AA70002

so all i would see after using the function/code/macro is below

AA70001
AA70002

any idea's?

Hi

It is possible to pull out the necessary data if it will always be in the same position, e.g

Please Login or Register  to view this content.

The reference to cells(1,4) is so that you can enter any number you want from 0-9.

Hope it is of use

Regards


Jeff

Originally Posted by 8RaM

i was wondering if it is possible to sort the following into number code or maybe hide everything but one number

like below i would want to hide everything but the codes with 7 in them

AA20001
AA20002
AA50001
AA70001
AB20001
AA70002

so all i would see after using the function/code/macro is below

AA70001
AA70002

any idea's?

"rng" is a define name range (ex: $A$1:$A$6)

=IF(ISERR(SMALL(IF(LEFT(rng,3)="AA7",ROW(INDIRECT("1:"&ROWS(rng)))),ROWS($1:1))),"",INDEX(rng,SMALL(IF(LEFT(rng,3)="AA7",ROW(INDIRECT("1:"&ROWS(rng)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

sorry maybe i didnt explain very well,

AA70001

the AA could be anything as its the first two letters of a persons last name could be AA to ZZ but i dont know many Mr ZZeds.

and then theres bank defines the 7 or 2 or even 5.

and then the 0001 defines when it was created so if Mr Arnold was first he would have AR70001 but then if Mr Argos joined in he would have AR70002.

so it is only the 7 i want to define or sort hope this makes it abit clearer any more info just post

Originally Posted by 8RaM

sorry maybe i didnt explain very well,

AA70001

the AA could be anything as its the first two letters of a persons last name could be AA to ZZ but i dont know many Mr ZZeds.

and then theres bank defines the 7 or 2 or even 5.

and then the 0001 defines when it was created so if Mr Arnold was first he would have AR70001 but then if Mr Argos joined in he would have AR70002.

so it is only the 7 i want to define or sort hope this makes it abit clearer any more info just post

Try this:

=IF(ISERR(SMALL(IF(MID(rng,3,1)="7",ROW(INDIRECT( "1:"&ROWS(rng)))),ROWS($1:1))),"",INDEX(rng,SMALL( IF(MID(rng,3,1)="7",ROW(INDIRECT("1:"&ROWS(rng))) ),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

i think i need a little more practice with excel only really know how to use a simple IF function and vlookup and recording a macro (not coding it) but thanks very much this will give me something to work at.