Please Help

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

Marcopolo

Hi,

I have for a while now been trying to find a solution to the problem I
face.

I work in an insurance company and receive a spreadsheet every week
with details on all the policies the company uses (it has around 500
rows and ten columns).

However, my team are only concerned with 164 of these scheme numbers
(there is an entire column with around 500 scheme numbers). Below is a
list of the 164 scheme numbers we use. We are not concerned with the
remainding 450.

I would like to create a macro which will automatically filter these
164 scheme numbers (and their corresponding information) from the
sheet. The possibilities I have looked at so far is delating the other
scheme numbers from the list in sheet 1 or entering the 164 scheme
numbers on sheet 2 and use the VLOOKUP function to compare the list
with the entire spreadsheet and only keep the 164 scheme numbers which
concern my department.

Please help!

SCHEME No.
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
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
 
Here's one solution:
Put the list you posted in a separate range on your worksheet, select it and
Insert>Name>Define>*give it a name, such as MyGroup*>OK
In a helper column, enter =COUNTIF(MyGroup,A2)>0 and copy it to the end of
your data.
If the number in A2 is one of your scheme numbers, TRUE will be returned.
Select all your data and Data>Filter>Autofilter. Filter for TRUE in the
helper column.
This assumes your scheme numbers begin in A2; if this is not true, make the
approriate change.
 
Thanks alot for taking the trouble to reply.

I have managed to do as you said and name the group of 164 scheme
numbers. I also made a help column directly next to it and entered the
formula as stated. However, I need a little bit of guidance as to how
this will work.

I have copied the 164 numbers onto sheet 2 and placed the help column
in the B column. Should I be doing this on sheet one? At the moment all
I am getting is TRUE all the way down the right hand side of my 164
numbers.

Marc
 
What you are doing is checking whether the scheme numbers are within
MyGroup.
You have to reference the cell within the master data as the second
parameter in the formula.
=COUNTIF(MyGroup,MasterListA2)>0
 
Back
Top