Comparing characters in cells

G

GVinIL

I am trying to create a column (column C) that contains values only
when the cell contents of column A and the cell contents of column B
are EXACT matches.

The characters in BOTH columns contain names of companies so there are
both alpha and numeric values.

The first FIVE characters (including spaces) of each line already
match EXACTLY... see example below.

I want column C to show a value only when ALL of the characters in the
cell in column A match EXACTLY to all of the characters in the cell in
column B.

I am running excel 2007 but could run an older version if needed.
....

The first five characters of each line are EXACT but the characters
after character number 5 MAY not be exact.
i.e.
Column A: Column
B: Column C:
MICROSOFT.PUBLIC.EXCEL MICROSOFT.PUBLIC.WORD
Able Body Labor Able Real Estate Usa, Inc.
ABN AMRO Abn Amro Inc.
Integrity Windows and Doors Integrity Windows and Doors
Integrity Windows and Doors
Central DuPage Hospital Centron Industries Inc.

As you can see in this example the first five characters of each
column match, but the rest of the cell characters do not match so
there is not a value in Column C. Except for Integrity Windows and
Doors. The characters in the cell in column A match EXACTLY to the
characters in the cell in column B so there is now a value in Column
B. This is a COMPLETE and EXACT match.

I apologize for the length of this post and am open for clarification
question.
Thank you in advance.
 
G

GVinIL

I am trying to create a column (column C) that contains values only
when the cell contents of column A and the cell contents of column B
are EXACT matches.

The characters in BOTH columns contain names of companies so there are
both alpha and numeric values.

The first FIVE characters (including spaces) of each line already
match EXACTLY... see example below.

I want column C to show a value only when ALL of the characters in the
cell in column A match EXACTLY to all of the characters in the cell in
column B.

I am running excel 2007 but could run an older version if needed.
...

The first five characters of each line are EXACT but the characters
after character number 5 MAY not be exact.
i.e.
Column A: Column
B: Column C:
MICROSOFT.PUBLIC.EXCEL MICROSOFT.PUBLIC.WORD
Able Body Labor Able Real Estate Usa, Inc.
ABN AMRO Abn Amro Inc.
Integrity Windows and Doors Integrity Windows and Doors
Integrity Windows and Doors
Central DuPage Hospital Centron Industries Inc.

As you can see in this example the first five characters of each
column match, but the rest of the cell characters do not match so
there is not a value in Column C. Except for Integrity Windows and
Doors. The characters in the cell in column A match EXACTLY to the
characters in the cell in column B so there is now a value in Column
B. This is a COMPLETE and EXACT match.

I apologize for the length of this post and am open for clarification
question.
Thank you in advance.
Click to expand...

Further example:
Column A: Column B: Column C:
abc1234 abc1233
123ert 123ern
4 5ngw 4 5ngw 4 5ngw
hl8 gi hl8 xi
n82 ./! n82 ./! n82 ./!

Hope this clears things up a bit. The first example I gave was too
long for the site and wrapped the text,
 
R

Ron Rosenfeld

I am trying to create a column (column C) that contains values only
when the cell contents of column A and the cell contents of column B
are EXACT matches.

The characters in BOTH columns contain names of companies so there are
both alpha and numeric values.

The first FIVE characters (including spaces) of each line already
match EXACTLY... see example below.

I want column C to show a value only when ALL of the characters in the
cell in column A match EXACTLY to all of the characters in the cell in
column B.

I am running excel 2007 but could run an older version if needed.
...

The first five characters of each line are EXACT but the characters
after character number 5 MAY not be exact.
i.e.
Column A: Column
B: Column C:
MICROSOFT.PUBLIC.EXCEL MICROSOFT.PUBLIC.WORD
Able Body Labor Able Real Estate Usa, Inc.
ABN AMRO Abn Amro Inc.
Integrity Windows and Doors Integrity Windows and Doors
Integrity Windows and Doors
Central DuPage Hospital Centron Industries Inc.

As you can see in this example the first five characters of each
column match, but the rest of the cell characters do not match so
there is not a value in Column C. Except for Integrity Windows and
Doors. The characters in the cell in column A match EXACTLY to the
characters in the cell in column B so there is now a value in Column
B. This is a COMPLETE and EXACT match.

I apologize for the length of this post and am open for clarification
question.
Thank you in advance.
Click to expand...


=IF(EXACT(A2,B2),A2,"")


--ron
 
R

Ron Rosenfeld

i'll be darned! It worked! Thank you!
Click to expand...

You're welcome. Glad to help.

One caveat: EXACT means exactly that. =EXACT("A","a") --> FALSE

If you want your comparison to be case-INsensitive, then use something like:

EXACT(UPPER(A1),UPPER(B1))


--ron
 
D

Dave Peterson

or just
=a1=b1

Ron said:
You're welcome. Glad to help.

One caveat: EXACT means exactly that. =EXACT("A","a") --> FALSE

If you want your comparison to be case-INsensitive, then use something like:

EXACT(UPPER(A1),UPPER(B1))

--ron
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro for comparing columns and highlighting matching/differences 14
Figuring out a lengthy formula. Probably quite simple... 3
Put parts of cell into separate columns 3
COUNTIF + ADDRESS Issue 1
VLOOKUP formula? 12
Would like cell data to be more editable and work with formula 2
Group List by Values in Column A and combine related row values inColumn B into 1 Cell 7
Comparing columns 5

Top