Help with conditional formatting (may be complicated)

[Rashie]

Hi All,

I could use some help with this particular challenge I'm facing.

Below is some sample data

				Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
Market	Departure Time	CXR & FlightNumber	Name	24 11 2016	25 11 2016	26 11 2016	27 11 2016	28 11 2016	29 11 2016	30 11 2016	01 12 2016
LONEGG	07:30		gina	£7.00	£11.20	£7.50	£8.40	£7.00	£7.00	£84.00	£8.40
LONEGG	09:00		richard	£125.00	£10.00	£12.00	£125.00	£6.00	£6.00	£6.00	£6.00
LONEGG	10:00		gina	£24.70	£8.40	£9.20	£2.00	£11.20	£9.00	£84.00	£4.00
LONEGG	12:00		richard	£125.00	£15.00	£12.00	£20.00	£10.00	£7.00	£7.00	£6.00
LONEGG	12:30		gina	£24.70	£13.30	£9.00	£24.70	£11.20	£9.20	£84.00	£8.40
LONEGG	14:30		gina	£24.70	£8.40	£7.00	£25.50	£9.20	£4.00	£75.00	£7.00
LONEGG	15:00		richard	£15.00	£15.00	£10.00	£20.00	£10.00	£7.00	£7.00	£7.00

<tbody>
</tbody>

The report pulls through the information in this ungodly format. The main person I'm interested in is Gina.
Each line represents a cost for a bus route from LON-EGG and the respective ticket prices for each time for each date.

The information I'm interested in highlighting through conditional formatting would be any price that Gina has set that is less or more than a £2 difference than (this is where it gets complicated) for any ticket Richard has set only if there is an hour or less between flights.

So for this particular example, I would really only want to know about Gina's 10:00, 12:30 and 14:30 tickets, should they differ from any ticket within an hour of them that has a £2 or more difference.
The reason I wouldn't be interested in the 07:30 ticket is because the next ticket isn't until 09:00 which is 1:30 later.
Any of Gina's prices that differ by £2 or more from any flight within an hour of one of Richards should show up red.

Thanks for your help - there is an ENORMOUS amount of data I need to work through so to flag them up red would be doing me a massive favor but I am completely stumped!!

Thanks

Rashie.

 

[Gerald Higgins]

OK so just to be totally clear, in the sample data provided, EXACTLY WHICH entries should be highlighted ?

 

[Rashie]

				Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
Market	Departure Time	CXR & FlightNumber	Name	24 11 2016	25 11 2016	26 11 2016	27 11 2016	28 11 2016	29 11 2016	30 11 2016	01 12 2016
LONEGG	07:30		gina	£7.00	£11.20	£7.50	£8.40	£7.00	£7.00	£84.00	£8.40
LONEGG	09:00		richard	£125.00	£10.00	£12.00	£125.00	£6.00	£6.00	£6.00	£6.00
LONEGG	10:00		gina	£24.70	£8.40	£9.20	£2.00	£11.20	£9.00	£84.00	£4.00
LONEGG	12:00		richard	£125.00	£15.00	£12.00	£20.00	£10.00	£7.00	£7.00	£6.00
LONEGG	12:30		gina	£24.70	£13.30	£9.00	£24.70	£11.20	£9.20	£84.00	£8.40
LONEGG	14:30		gina	£24.70	£8.40	£7.00	£25.50	£9.20	£4.00	£75.00	£7.00
LONEGG	15:00		richard	£15.00	£15.00	£10.00	£20.00	£10.00	£7.00	£7.00	£7.00

<tbody>
</tbody>

So it's just for Gina if there is a difference of more than £2 for any other ticket that is within an hour for the same day. I used £2 for a starting figure which proved to be a bit rubbish but the amount will be amendable.

Thanks again!

Rashie.

 

[AOB]

Okay try this for starters - recommend checking this with a fine toothcomb as, like you say, it's complicated...

Some assumptions I've made :

• your data starts in A1 (so "Market" is in column A, "Name" is in column D and the dates start from column E and toward the right)
• the days are in row 1, the dates & column headers in row 2 and the actual information starts in row 3
• all the cells are formatted correctly (i.e. times in column B as "hh:mm", prices as numbers / currency etc.)
• the data is contiguous (i.e. no gaps or blank rows - and for every row of data, there is a value in column A (e.g. "LONEGG")
• the sheet is called Sheet1 (change accordingly)

If this is correct, try this :

1. Go to Formulas > Name Manager
2. Click New...
3. Name = Data
4. Refers to : =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$2:$2))
5. Click OK
6. Click Close
7. Select all the columns containing prices (e.g. columns E through L in your example)
8. Go to Home > Conditional Formatting > New Rule...
9. Select "Use a formula to determine which cells to format"
10. In the textbox for "Format values where this formula is true:" enter the following : =AND($D1="gina",IFERROR(MATCH(1,(OFFSET(Data,1,3,,1)<>"gina")*((ABS(OFFSET(Data,1,1,,1)-$B1))<=(1/24))*((ABS(OFFSET(Data,1,(INDEX(COLUMN(E1),1)-1),,1)-E1))>=2),0),0)>0)
11. Click the Format... button and define whatever formatting you want (e.g. red fill etc.)
12. Click OK

Does that work?...

 

[AOB]

In the example where you showed which cells should be highlighted, should the following not also be highlighted :

• Monday 28th @ 10am (Richard has a flight 1 hour earlier which is £5.20 cheaper)
• Tuesday 29th @ 10am (Richard has a flight 1 hour earlier which is £3 cheaper)
• Thursday 1st @ 10am (Richard has a flight 1 hour earlier which is £2 cheaper)

?...

 

[Rashie]

It seems to be, so thanks for the effort you put into that for me.
You know what's weird, I've never noticed the "name manager" before...! Learning something new every day!

Rich

 

[AOB]

No worries - sure isn't that the whole point?

You could just select all the data and name it that way but the method above makes it dynamic - so if you add more dates to the right or times to the bottom, the named range automatically extends to include it (hence why I made a point of asking if the data was contiguous)