Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 9th 07, 04:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 26
Default 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





  #2   Report Post  
Old November 9th 07, 04:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
TEST for items in 2 columns to MATCH? nastech Excel Discussion (Misc queries) 4 September 22nd 07 05:29 AM
How to match and compare two spreadsheets Brad Excel Worksheet Functions 1 June 28th 07 09:32 AM
compare & match text Susanne Excel Worksheet Functions 5 May 22nd 07 05:34 PM
Test IF Two Numeric Values Match (from two separate Dynamic Ranges) Sam via OfficeKB.com Excel Worksheet Functions 3 August 14th 05 12:20 AM


All times are GMT +1. The time now is 12:57 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017