Filter out one list of text in another list.
 

Hi,

My problem is the folowing.
I'm a radio amateur and I want to choos a good callname.
consist of a prefix followed by 3 or 2 letters.

On the site of our department that gives the calls I found a list with all
the used calls (in excel format).
I want to revers it!

With some trick's I managed to create a huge list with all the calls
starting from AA to ZZZ
(there are 18252 in total!)

Now I want to filter out the calls that are already in use, so I will only
see the free calls.

I'm searching now the whole night and I can't find any good function that
does this.
I started recording a macro.

This is my idee:
- create a map with 2 sheets, sheet 1: all the calls, sheet 2: used calls
- go to sheet 2 and select the first from the list
- start recording macro
- copy that value
- go to sheet 1 and press Ctrl+F, and choose replace
- paste the value
- enter BUSY as replacement
- go back to sheet 2 and select the 2nd from the list
- stop recording macro

Now it should by pressing the macro's keyboard shortcut replace all the busy
calls by BUSY
It doesn't :(

I think my problem is in the copy - past, I get the following source code of
my macro

Sub change()
'
' Change Macro
' Recorded 4/09/2003 by Gery Duyck.
'
' Shortcut: CTRL+V
'
Range("A1").Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
Cells.Replace What:="AAA", Replacement:="BUSY", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet3").Select
Range("A2").Select
End Sub


You see? he just pasted the value (AAA) in stead of a paste command in the
source code.
Can somebody help ???
How can I get this script working or is there a better way to do this.

Kind Regards,
Gery D.

Filter out one list of text in another list.
 

If you are interested I send you an add-in that can do this and much more.

RBS


"Géry Duyck" wrote in message
. be...
Hi,

My problem is the folowing.
I'm a radio amateur and I want to choos a good callname.
consist of a prefix followed by 3 or 2 letters.

On the site of our department that gives the calls I found a list with all
the used calls (in excel format).
I want to revers it!

With some trick's I managed to create a huge list with all the calls
starting from AA to ZZZ
(there are 18252 in total!)

Now I want to filter out the calls that are already in use, so I will only
see the free calls.

I'm searching now the whole night and I can't find any good function that
does this.
I started recording a macro.

This is my idee:
- create a map with 2 sheets, sheet 1: all the calls, sheet 2: used calls
- go to sheet 2 and select the first from the list
- start recording macro
- copy that value
- go to sheet 1 and press Ctrl+F, and choose replace
- paste the value
- enter BUSY as replacement
- go back to sheet 2 and select the 2nd from the list
- stop recording macro

Now it should by pressing the macro's keyboard shortcut replace all the
busy
calls by BUSY
It doesn't :(

I think my problem is in the copy - past, I get the following source code
of
my macro

Sub change()
'
' Change Macro
' Recorded 4/09/2003 by Gery Duyck.
'
' Shortcut: CTRL+V
'
Range("A1").Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
Cells.Replace What:="AAA", Replacement:="BUSY", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet3").Select
Range("A2").Select
End Sub


You see? he just pasted the value (AAA) in stead of a paste command in the
source code.
Can somebody help ???
How can I get this script working or is there a better way to do this.

Kind Regards,
Gery D.

Filter out one list of text in another list.
 

This wors with a call or 20.
But with a list of aprox 18500 calls it crashes.

either crashes excel,
or sometimes give me only 0 or only 1's

Gery Duyck

"dermot" schreef in bericht
...
Gery

try an array formula

in your AA-ZZZ list of possible calls, put this formula
in the next blank column, next to each cell. I'm
supposing the list starts in A1, change as you need to

=SUM((A1=UsedCalls)*1)

when you type this in, press Ctrl-Shift-Enter to tell
Excel this is an array formula (look in help or search
net for more on these).

You need to name a range called UsedCalls, this is simply
the set of cells contaiinng the used call signs.

Copy down to other cells. It will either give 0 or 1, the
1's mean the call has been used. Sort the result and
there you have all the unused calls together.

Dermot Balson
Free VBA code for user interfaces, internet connectivity,
encryption
http://www.webace.com.au/~balson/Ins...l/Default.html
Last updated August 2003

-----Original Message-----
Hi,

My problem is the folowing.
I'm a radio amateur and I want to choos a good callname.
consist of a prefix followed by 3 or 2 letters.

On the site of our department that gives the calls I
found a list with all
the used calls (in excel format).
I want to revers it!

With some trick's I managed to create a huge list with
all the calls
starting from AA to ZZZ
(there are 18252 in total!)

Now I want to filter out the calls that are already in
use, so I will only
see the free calls.

I'm searching now the whole night and I can't find any
good function that
does this.
I started recording a macro.

This is my idee:
- create a map with 2 sheets, sheet 1: all the calls,
sheet 2: used calls
- go to sheet 2 and select the first from the list
- start recording macro
- copy that value
- go to sheet 1 and press Ctrl+F, and choose replace
- paste the value
- enter BUSY as replacement
- go back to sheet 2 and select the 2nd from the list
- stop recording macro

Now it should by pressing the macro's keyboard shortcut
replace all the busy
calls by BUSY
It doesn't :(

I think my problem is in the copy - past, I get the
following source code of
my macro

Sub change()
'
' Change Macro
' Recorded 4/09/2003 by Gery Duyck.
'
' Shortcut: CTRL+V
'
Range("A1").Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
Cells.Replace What:="AAA", Replacement:="BUSY",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet3").Select
Range("A2").Select
End Sub


You see? he just pasted the value (AAA) in stead of a
paste command in the
source code.
Can somebody help ???
How can I get this script working or is there a better
way to do this.

Kind Regards,
Gery D.


.