Alpha Marking a list

V

vamosj

I have a long list of movies that I work with and I want to be able t
set up a macro that will search through my master list of movies, fin
the beginning of each section (all in alphabetical order,
A.....B.....C... So on) and right next to it put the marker of A, B,
and so on. This way when someone is looking at the list of movies t
request one they can find the location of the let's say "M" movies
lot faster than digging through the list. (Note, this is a printe
list). I can do the offset but what I'm trying to get is how do I hav
the search string set up so that it will search for the first ro
starting with A, B, C...... etc...

I am currently using an i = A but of course that isn't working.

If someone could help me out on this one it would be very muc
appreciated.

Thanks,


J. Vamo
 
A

Arvi Laanemets

Hi

No need for macro. The simple Autofilter will do.

Insert a column (left to your list maybe, so movie names will be in column B
The first row will be a header row, so it'll be something like
Section, Movie, ...
Into cell A2 enter the formula
=IF(B2="","",LEFT(B2,1))
and copy it down for at least as much rows as you have movies. Select any
cell in your list, and set Autofilter on (Data.Filter.Autofilter)

Now, when you search for movie starting with character p.e. "B", set filter
to Section column to "B".
 
D

Debra Dalgleish

You can use a formula to extract the first letter, and hide the
duplicates with conditional formatting --

Assuming the titles are in column A, insert a blank column to the left.
In cell A2, enter the following formula: =LEFT(B2,1)
Copy the formula down to the last row of data, e.g. A1000.
Select cells A2:A1000
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =A2=A1
Click the Format button
 
Top