Formula to recognise one word from multiple words in the same cell

**JLucy** · 06-27-2013, 05:30 AM

I have a number of sheets within a workbook each of which are assigned to a specific person for weekly data entry. I have set up another sheet which gives me an overview of the week, 2 columns of 5 cells each per day into which data is taken from the persons sheet if particular words are entered. So far I've used this and achieved reasonable results (although the &CHAR(10) is not functioning - another problem

).

&IF(OR(Monday!G16="small")," Jayne "&Monday!G19,"") &CHAR(10) &IF(OR(Monday!H16="small")," Alex "&Monday!H19,"") &CHAR(10) &IF(OR(Monday!I16="small")," Tom "&Monday!I19,"")

&IF(OR(Monday!G16="tall")," Jayne "&Monday!G19,"") &CHAR(10) &IF(OR(Monday!H16="tall")," Alex "&Monday!H19,"") &CHAR(10) &IF(OR(Monday!I16=" tall")," Tom "&Monday!I19,"")

However, I'd like to tweak it so that the formula allows recognition of 'tall' or 'small' even when they are both entered in the originating cell (and also not character sensitive), rather than having to be the only word entered. I tried using * but it threw a wobbly!

Any ideas? There's probably a simple answer, but I'm just starting using formulae so I don't know all the tricks.

Thanks

**Richard Buttrey** · 06-27-2013, 05:37 AM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Also indicate what you are trying to do. It may be that you can achieve your aim in a simpler way.

**JLucy** · 06-27-2013, 06:37 AM

OK Here's a demonstration worksheet. Demonstration.xlsx I've only populated Monday.

It's for teachers to request the use of laptops in their lessons. We have two trolleys and need to see at a glance if the same one has been requested by two teachers at the same time. Sometimes a teacher wants to request both - see Sadie Monday 2; and Adam Monday 4. However, those have not gone through to the 'Laptops' sheet.

Also, on the 'Laptops' sheet it would be great if the carriage return would work to separate the items shown at 1 and 3.

**Richard Buttrey** · 06-27-2013, 07:17 AM

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

You are also complicating things immensely by merging cells. Avoid this at all costs. They are a real nuisance and can seriously compromise other areas of Excel.

I suggest you change the way you capture data. A typical database would have columns for
Date
Staff Name
Course ID
Laptop Type (entries being Small or Large)

and maybe others - I don't understand what the numbers or 'pupils' means
Typically you'd use a date entry form to allow staff to update their details. Once you have a flat two dimensional table then reporting against it will be simple.

**JLucy** · 06-27-2013, 09:41 AM

I agree that in an ideal world the data entry would be different. However, we do not reside in an ideal world and I have to work with what I have got - which are the sheets as they are set up - which shows the individual teachers timetables in a format which makes it easy for them to work with.

My formula may not be the elegant solution that I was hoping for, or that would make a programmer's heart sing, but it does work - to a point. I was hoping for a little help to extend it / minimalise it a bit. If that is not possible with the present layout I'll just have to live with it the way it is.

Thanks anyway.

**Richard Buttrey** · 06-27-2013, 11:48 AM

Hi,

Given your data layout I really can't see a straightforward solution and one that will not take a disproportionate time in this general forum. Maybe someone else will have a different view in which case then great.

You may find someone in the Commercial Services forum who might be happy to take it on for a small reward. In which case post a request in there and cross reference it to this thread.

The Char(10) is working but you will need to format the cell to wrap text. Your IF formulae don't need any OR conditions since there are only two options. You may find the following works better and will pick up small or tall laptops. This example is B5 on the laptops sheet. You'll need to replicate elsewhere.

Formula:

=IF(LEFT(Monday!B12,7)="Laptops"," SALLY "&Monday!B15&CHAR(10),"")&IF(LEFT(Monday!C12,7)="Laptops"," JOE "&Monday!C15&CHAR(10),"")&IF(LEFT(Monday!D12,7)="Laptops"," SADIE "&Monday!D15&CHAR(10),"")&IF(LEFT(Monday!E12,7)="Laptops"," ADAM "&Monday!E15&CHAR(10),"")&IF(LEFT(Monday!F12,7)="Laptops"," **** "&Monday!F15&CHAR(10),"")&IF(LEFT(Monday!G12,7)="Laptops"," JAYNE "&Monday!G15&CHAR(10),"")&IF(LEFT(Monday!H12,7)="Laptops"," ALEX "&Monday!H15&CHAR(10),"")&IF(LEFT(Monday!I12,7)="Laptops"," Tom "&Monday!I15,"")

However as I said I'd really urge you to change the layout. As I said a common simple data input form for all staff to use would build your database each time someone adds a record and you won't need a sheet for every staff member or day of the week. OK there's a little retraining of staff but given it would be much simpler for them you're on a winner there too.