S
Steve Walford
A while ago I posted a problem where I had a list of vehicle
registration numbers and wanted to compare the list against a list of
3000 registrations Ken Wright provided me with the following solution
which does exactly what i wanted
And the other way is to have your 50 reg entries in a list somewhere.
Name the list using Insert
/ Name / Define and call it mylist.
Now against your 3000 record list, assuming your reg entries are in
Col A starting in A2, in a
spare column (assume H), in H2 put the following formula:-
=COUNTIF(mylist,A2) and then copy the formula down.
Against every record that is in your desired list you will now see a
1. If the record is not in
the list it will have a 0. Use Autofilter to filter on just the 1s,
select the data, do Edit / Go
To / Special / Visible cells only - Do Edit / Copy, select a new
location and do Edit / Paste.
I have now progressed to trying to use the formula in similar
situation but cannot get it to work
I producea list of service dates for vehicles at six week intervals, I
provide the seed date say 01/01/2003 in cell B2 the other service
dates are calculated by having the following in C2 =B2+42 this is
carried across the spreadsheet say from B2 to W2 (vehicles are
serviced every 6 weeks (6x7 = 42))
I need to ensure sevice dates do not fall on a bank holiday, I have a
list of the relevant bank holidays and thought the following would
work
I put the bank holidays in a list and named it holidays. I thought I
could use a spare column say X and put the follwing formula in X2
=COUNTIF(holidaysB2:W2) it does't work. Can it be made to work or can
anyone suggest another way of makig it work
Many thanks
Steve
Remove nospam from email address to reply
registration numbers and wanted to compare the list against a list of
3000 registrations Ken Wright provided me with the following solution
which does exactly what i wanted
And the other way is to have your 50 reg entries in a list somewhere.
Name the list using Insert
/ Name / Define and call it mylist.
Now against your 3000 record list, assuming your reg entries are in
Col A starting in A2, in a
spare column (assume H), in H2 put the following formula:-
=COUNTIF(mylist,A2) and then copy the formula down.
Against every record that is in your desired list you will now see a
1. If the record is not in
the list it will have a 0. Use Autofilter to filter on just the 1s,
select the data, do Edit / Go
To / Special / Visible cells only - Do Edit / Copy, select a new
location and do Edit / Paste.
I have now progressed to trying to use the formula in similar
situation but cannot get it to work
I producea list of service dates for vehicles at six week intervals, I
provide the seed date say 01/01/2003 in cell B2 the other service
dates are calculated by having the following in C2 =B2+42 this is
carried across the spreadsheet say from B2 to W2 (vehicles are
serviced every 6 weeks (6x7 = 42))
I need to ensure sevice dates do not fall on a bank holiday, I have a
list of the relevant bank holidays and thought the following would
work
I put the bank holidays in a list and named it holidays. I thought I
could use a spare column say X and put the follwing formula in X2
=COUNTIF(holidaysB2:W2) it does't work. Can it be made to work or can
anyone suggest another way of makig it work
Many thanks
Steve
Remove nospam from email address to reply