ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   compare and match test (https://www.excelbanter.com/excel-worksheet-functions/165463-compare-match-test.html)

Little pete

compare and match test
 
Hi

I would like to know if the following is possible to complete as a
conditional formula.

I would like to look at one row of data, look at the first value (child) and
then go across and look at the second value (parent). Then test to ensure
that against a theoretical table that the relationship is correct. ie has
the user entered in the correct parent value for the child value.

Data set
Child Parent
BC001 BI001 Correct - Cell colour left blank
A5001 BI001 Incorrect - Cell colour turned red


Theoretical table
Child Parent
BI001 MH002
BC001 BI001
A5001 BC001

Many thanks Peter





ShaneDevenshire

compare and match test
 
Hi Pete,

Yes, you can use something like:

1. Select the second column of your input area, I assumed the data was in
A1:B4, so I selected B1:B4.
2. Choose Format, Conditional Formatting, Formula is and enter the
following formula:

=VLOOKUP($A1,$A$14:$B$17,2,FALSE)<$B1

I had put the theoretical table in the range A14:B17. Of course this
assumes that for each item in column A there is a unique item in column B.
There is a different solution if there can be more than one item in column B
for a given entry in column A.

--
Cheers,
Shane Devenshire


"LITTLE PETE" wrote:

Hi

I would like to know if the following is possible to complete as a
conditional formula.

I would like to look at one row of data, look at the first value (child) and
then go across and look at the second value (parent). Then test to ensure
that against a theoretical table that the relationship is correct. ie has
the user entered in the correct parent value for the child value.

Data set
Child Parent
BC001 BI001 Correct - Cell colour left blank
A5001 BI001 Incorrect - Cell colour turned red


Theoretical table
Child Parent
BI001 MH002
BC001 BI001
A5001 BC001

Many thanks Peter






All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com