Search for a string in a Range

Hi,

I have a list of words/codes. For each word/code, I am trying to identify if it appears in a range of cells (either as the entire cell or as a part of the cell). I'm not quite sure how to use the Find/Search/Countif functions when entering using an array, and I thought this would be an excellent means to find out how to fully utilise them!

I've attached a representation of the database I'm working with.

So to illustrate this scenario, the representative database comprises people who have attended a course which is run over 11 days.

There is a list of names which I wish to check if they have attended the course on ANY day.

The objectives....
For each name on the list (Employee Table - Column M), I wish:
a) To see if the person has attended any days (i.e. appear in the Reference Table range) - Output "Yes/No"
b) To list which days they have attended - Output "01, 02, 03, 04" etc.
c) To list the cell that the employee's name appears in (B2, B6, D7, D11)
d) To count the number of the employee has attended.

To make things a little more interesting (and to ask advice about how to best structure the database!), I've used two different layouts for the database.

The first one (opt. 01), I have used a separate cell for each employee who attends on a particular day
e.g. for Day one (Row 3), there were four attendees. These names appear in Cells B3:E3. The issue being is that if these are in an excel sheet, I need to reserve an unknown number of columns as I don't know the maximum number of people will be attending

For the second layout (opt. 02), I have used one cell to store all employees who attended a particular day, separated by a comma.
e.g. for Day one (Row 3), there were four attendees. These names appear in Cell B3: "DAVE, GRAEME, STUART, IAIN"
This is more preferable (it doesn't really matter if there are one or a hundred attendees, they're all bunged into one cell/column.

I would be very interested/grateful to understand how to search for the same output data required as above (a-d)

Using VBA, I can then use the separator (in this case a comma) to enter the range simply into an array (Using a combo of Instr() and Mid() commands.


I look forward to learning from your replies and thank you for taking the time to read my question! :o)

Kind Regards, John





p.s. I will be ultimately be doing this using VBA, but I think it would greatly improve my understanding (and hopefully many others!) of using formulae in Excel (such as Find/Search/Countif by array entre Ctrl+Enter(or not)!

I'm not sure if all of these are possible through formulae alone, but which ever can be done would be really useful to see!

Thanks mcamahobt
That nearly solves the problem - except if there was an attendee, "Johnathan", then this would provide a false positive for "John" on the list.

I can see that this could be partially accounted for by introducing a "," into the search so:

Please Login or Register  to view this content.

would become

Please Login or Register  to view this content.

This would however cause a problem with names that weren't succeeded with a comma - such as singular attendees or the last one on the list, so presumably an additional condition would be required - such as an OR operator?


Is there a way of using the Match function for a range spanning more than one column - For example for Option 01?

I understand that the number of days may be an issue, but can the position of the first occurrence be returned (such as the Cell e.g."B3" and/or the Position in the cell of the first letter of the name - e.g. Character 7)

Thanks again mcmahobt - this formula does indeed return the row#.

Unfortunately with regards to issue of false positives "Johnathan" incorrectly being identified as a match for "John", this is not an option for my specific application. I used names as a simple way of illustrating the issue. So more appropriate values would be "AB1253" being incorrectly returned as a match for "AB125" which is an entirely different item, or similarly "Wheeltrim" being seen as a match for "Wheel"...