Please Help!

  • Thread starter Thread starter Marcopolo
  • Start date Start date
M

Marcopolo

Hi Folks!

I need to create a macro for my work and I have been trying all day t
do it without success. None of my workmates can offer a bette
solution.

The problem: I have been asked to write a macro which will filter ou
approximately 160 reference numbers from a list of approximately 500.

It takes the following structure:

Scheme No Area Dept Person
9876gf34 A G.P.P D Smith
9898gf54 B M.P.U T Jones

I receive a different spreadsheet every week with 500 numbers. I need
macro that when activated will filter out the 160 relevent numbers an
paste them in a seperate sheet (possibly sheet 2). I have attempte
doing a macro with the autofilter. However, it only works if th
numbers are in a given order (if the numbers are jumbles up it doesn'
work). The filter only needs to work on the A Column (i.e. Scheme No
and all the other relvent information shouls be copied according to th
Scheme No.

I seem to have rambled on somewhat! I hope someone can help in som
way!:confused:

Thank You

Mar
 
macro that when activated will filter out the 160 relevent numbers

Any chance of a clue as to what makes the 160 numbers "relevant"?

Rgds,
Andy
 
Dear Marc
If the filter only works with the numbers in a given
order, perhaps it is worthwhile getting your macro to
perform the sort first, then do the filter, copy, paste
etc. If there is a reason why you don't want to sort the
data first, please post back for further help

Kind regards and seasons greetings

Paul
 
Thanks for the reply guys!

I about 10 coulmns each having around 500 rows. I only want the rows
with certain Policy numbers. There is a column names Scheme No's and it
contains a list of around 500 Scheme Numbers. I want the Macro to
filter out all the policy numbers. Here is a list of them:

here is a list of all the schemes
132DR617 132NR375 132HR307 132FR098 132ER077 132BR673 131YR590
131XR795
131WR844 131TR360 131RR019 131HR855 131FR856 131ER198 130YR845
130DR627
130CR768 129XR963 129WR488 129MR530 129MR329 129HR392 129DR162
129CR632
128YR992 128WR679 128KR231 128HR949 128DR423 128CR299 126PR913
126MR850
126KR586 126KR278 126HR123 126ER573 126DR061 125XR858 125TR725
125MR889
125LR880 125JR762 125ER828 125ER715 124XR564 124XR262 124WR592
124UR257
124UR150 124PR243 124NR600 124NR390 124LR391 124JR218 124ER610
124CR654
124BR487 123XR950 123XR636 123UR713 123UR033 123RR580 123PR202
123MR618
123MR295 123LR533 123KR787 123KR506 123JR302 123FR520 123ER844
123BR220
122UR499 122PR982 122PR323 122PR070 122KR450 122HR796 122GR909
122GR845
122GR046 122FR559 122FR495 122ER620 122DR410 121XR997 121XR274
121XR036
121NR417 121KR754 121KR446 121KR257 121JR053 121DR833 121CR736
120YR059
120TR677 120RR726 120PR489 120MR334 119YR350 119XR140 119WR470
119UR400
119UR141 119TR471 119KR776 119HR670 119HR664 118YR648 118YR001
118WR466
118WR002 118TR964 118TR348 118PR004 118NR712 118MR481 118MR005
118GR273
118FR008 118CR772 118BR486 117UR993 117RR994 117PR967 117NR995
117MR931
117JR997 117GR998 117FR400 117ER999 117DR438 117DR401 117CR914
116GR542
116FR299 116BR151 115XR499 114NR586 114PR494 113GR127 111HR051
110GR517
110KR424 109RR689 108UR885 108FR530 108XR338 108YR008 107FR386
106NR201
106XR116 105XR167 104PR345 103JR642 103TR093 102RR824 102HR736
102JR272
102NR075 101CR780 100GR466 100XR189

I would like them placed on a seperate sheet/worksheet.

Hope you understand guys and can help.

Thanks,

Marc
 
Hope you understand guys

Not really - scheme #s in your original message (eg: 9876gf34) bear no
resemblance to policy #s in your follow-up.
here is a list of all the schemes
Click to expand...

164, in fact. IF (big if) they're in a genuine list, you could use a helper
column with a formula such as

=VLOOKUP(A1,$H$1:$H$164,1,FALSE)

This will return #N/A for any scheme # not in the 164 list. Then you could
(custom) AutoFilter your table on the helper column (formula result does not
equal #N/A).

HTH (unlikely),
Andy
 
Hi,

Thanks alot for your time guys. I like the idea of the VLookup.
However, how do I go about comparing the raw list of numbers to my list
of 164 numbers. I think this would work if I could do this.

Marc
 
Back
Top