Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula (not adv. filter) to list unique values from list | Excel Worksheet Functions | |||
use list box text for advanced filter on dblClick | Excel Discussion (Misc queries) | |||
How to filter list from pre-existing list | Excel Discussion (Misc queries) | |||
Filter the results of a list based on a previous vlookup against the same list | Excel Worksheet Functions | |||
filter list of text for unique entries using formulea | Excel Worksheet Functions |