Text List in Excel

Microsoft Excel 2004 for mac
April 1, 2010 at 12:03:17
Specs: Macintosh

Using Excel for mac 2004, version 11

In column 1 I have a list of schools that currently
receive product A. In column two I have a list of
schools that receive product B.

Is it possible to create another column that tells if
schools from Column 1 receive both A and B
products? What would that formula be?

Here is the catch, some schools are represented
different. example "oxford University" versus "Oxford
Univ." Will I have to go through and fix this first or is
there a way to match the schools based on just the
first couple of letters? (I'm guessing not)


See More: Text List in Excel

April 1, 2010 at 13:27:22
You can try something like:

=IF(ISNA(MATCH("*"&RIGHT(A1,10)&"*",$B$1:$B$50,0)),"No Match","Match")

Which will attempt to match the first five ten letters in A1 with the cell range B1 through B50
If you are checking more than fifty cells, modify to suit your needs.



Report •

April 2, 2010 at 04:47:34

For the future I suggest you use data validation to ensure that standardized names are always used.

Make a list of allowed, standardized names.
For example AA1 to AA120
Select the range AA1 to AA120
From the Menu select Insert - Name - Define...
In the dialog box that opens enter SchoolList in the box at the top. Note that spaces are not allowed in the name.
The box at the bottom should already contain =Sheet1!$A$1:$A$120 (or whatever the name of the worksheet is).
Click Add and then OK

Now for any cell that where a School name has to be entered, select the cell.
From the menu select Data - Validation...
In the dialog box that opens, select List from the 'Allow' drop-down
In the 'Source:' box enter =SchoolList
Click OK

The selected cell will now contain a drop-down with all the school names and it will not allow names or spellings that are not in the list.

To make a standardized list put "NAMES" into row1 of an empty column.
Take existing names from all locations used for names and paste them into that single column.
Select this extended list and from the menu select Data - Filter - Advanced filter...
(If you get a message about not knowing if there is a label in the first row, just click OK)
In the dialog box that opens, select 'Copy to another location'
Check 'Unique records only'
Use the 'Copy to' box to select a single cell, say the first row of the next column.
Click OK.

All Unique names will be placed in the new column (duplicates that differ only by capitalization are ignored).
Now select the list and from the Menu select Data - Sort...
From the dialog box select 'continue with current selection'
Select 'Sort by' NAMES and Ascending and 'My range has' Header row
Click OK and the list will be sorted.

Now edit the list to remove unwanted 'duplicates' and edit names to create your standard list.
Use this list as the source of your drop-down.

If you add names of new schools to a named list, the new name should appear automatically in all drop-downs.

If you need to add a name at the end of the list,
Insert a new cell before the last name, select and copy the original last name into the empty cell (don't drag it) and enter the new name over the previous last name.
The named range will have expanded by one row to include the new name.

I see that you are using Excel v11 for the Mac
I have no experience with this, so you may have slightly different menus etc. but the process should be the same


Report •
Related Solutions

Ask Question