calculate data and find outliers

[junaid44]

Dear all,

I am writing to request a macro/function/formula, which can find duplicate names and match them up, between both sheets and run logic calculations, as explained below:

The aim of the macro is to find the same name and date from (sheet2) and match it to the , same name and date in sheet1, and execute the calculation logic below:

outlier calculation logic:
if name's prices (sheet1 - column G) fall under the high & low Range (from sheet2 - range 1), then leave blank, else if they do not, highlight the prices in sheet1, in red.

if name's prices (sheet1 - column G) fall under the high & low Range (from sheet2 - range 2), then leave blank, else if they do not, highlight the prices in sheet1, in yellow.

Also, if any outliers found extract them in to the sheet2, under the heading outliers. please note, outliers can be more than

Please see the attachment below, for further details:
outliers.xls

the example can be seen in the sheet1, which is the desired output.

Thank you for your time and help.

[Bob Phillips]

A workbook with a few manually calculated results would help.

[p45cal]

The attached is a version of your sheet with conditional formatting in column H (for the moment, only to compare with your highlighting).
The red highlighted area (L1:U14) is not needed (can be deleted) but just shows the derivation of the formulae used in the conditional formatting; the formulae used are in cells T2 and U2, which are in turn derived from the formulae in M2 and Q2 respectively.
I've made the assumption that your "fall under" means "fall within".

Note: While saving and attaching the sheet I tried to save as an .xls file but I've used conditional formatting formulae which use data from a different sheet from the one which has the conditional formatting, and that wasn't allowed until after Excel 2007 or Excel 2010, so I hope you're using an Excel version where this is allowed.