Compare two columns for typos

[Patti Backman]

Hello,
I have two columns of data. The first (column A) is a master part list that
has no typos, the second (column B) is a part list of parts stored at my off
site storage facility. These part #'s are typed in manualy and sometimes
contain typos that mean we can not find the parts when we need them. How do
I ask the question: Are there any part numbers in column B that do not match
a part number in column A? This would highlight a typo for us.
Thank you in advance.

 

[Mike H]

Hi,

Select colmn B data and then

Format|Conditional format - Formula is

and use this formula

=COUNTIF(A:A,B1)=0

Pick a colour (say) Red
Ok
Any numbers in col B and Not in Col A will be highlighted red.

Mike

 

[Lars-Åke Aspelin]

On Sat, 3 Jan 2009 13:54:00 -0800, Patti Backman <Patti

Hello,
I have two columns of data. The first (column A) is a master part list that
has no typos, the second (column B) is a part list of parts stored at my off
site storage facility. These part #'s are typed in manualy and sometimes
contain typos that mean we can not find the parts when we need them. How do
I ask the question: Are there any part numbers in column B that do not match
a part number in column A? This would highlight a typo for us.
Thank you in advance.

Try this formula in cell C1:

=IF(COUNTIF(A$1:A$100,B1),"","TYPO")

change the 100 to cover the number of rows with parts in column A

Copy the formula down as far as you have parts in column B.

The result will be the string "TYPO" in column C wherever the part in
column B is not found in column A.

Hope this helps / Lars-Åke

 

[T. Valko]

One way...use conditional formatting to highlight cells in column B that do
not match an entry in column A.

I'm assuming that case matches aren't required. That is:

A100
a100

Are considered a match.

Assume the master list is in the range A1:A10
Assume the list to check is in the range B1:B5

Select the range B1:B5
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=ISNA(MATCH(B1,A$1:A$10,0))

Click the Format button
Select the Patterns tab
Pick a color to highlight the cells with
OK your way out

Cells in column B that do not match any cell in column A will be
highlighted.

 

[Shane Devenshire]

Hi,


To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:

=AND(B1<>$A$1:$A$3)

5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:

=AND(B1<>$A$1:$A$3)

5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Rick Rothstein]

Another method is to use Data Validation. This will allow your user to
either type the value in or pick it from a list (their choice) and will
signal a customizable error message if a bad value is entered. Select Column
B and then select Data/Validation from Excel's menu bar; select the Settings
tab on the dialog box that appears; select "List" from the "Allow" drop down
box, then put this in the "Source" field....

=$A$1:$A$123

changing the start and end cell for the range to match the cell range for
your master parts list (but retain the absolute cell references). While you
are there, you can also customize the error message by clicking the "Error
Alert" tab on the dialog box. OK your way back to the worksheet. Click in
any cell in Column B and either type a value or click the drop down arrow to
pick the value from the list that appears. (Also try typing in a bad value
to see the error message handling.)

 

[Patti Backman]

Thanks this looks great - I will try all the ideas and see what works best
for us - Thank you to everyone for our help!

Patti