How to compare two lists by only the first word?

S

samtheman509

Board Regular
Joined
Sep 1, 2015
Messages
54
I have two lists of company names in columns 1 and 2, and I want to see which companies in column 1 are already in column 2. However the company names are slightly different on the end, for example column 2 might have LLC or Co at the end while column 1 doesn't. Is there a way for me to compare the two lists just based off the first word? Ex: Staples and Staples Co would match even tho they are not the same?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Here's one option:
Excel 2010
ABCD
StaplesSears
Office MaxStaples Co
Sears CoJC Penney's
Home DepotLowe's
Jackson'sHome Depot
McDonald's

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A1,IFERROR(FIND(" ",A1)-1,LEN(A1)))=LEFT($B$1:$B$10,IFERROR(FIND(" ",A1)-1,LEN(A1)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in D1, then you can copy it and paste it down the column. A 0 means that the company in A is not in column B, a 1 means it is, and anything else basically means you have an empty cell, or multiple matches. This compares the first word of the company in column A with the same length in column B. But probably if you already have some discrepancies, then this will probably not catch them all.

If you want you can also use this formula as a Conditional Formatting formula.

Let me know if this helps.
 
Last edited:
Upvote 0
Are you asking me or Eric?
Match doesnt matter about number of words...
example: "Staples Co.*" will match with "Staples Co. LLC (#1 Office company in teh world!)"
 
Upvote 0
It gets complicated pretty quick. The new formula is in E1. Note that in my sample sheet, Sears Co in A3 no longer matches Sears in B1. This is also true about easy2understandexcel's formula. That will never match if the name in column A is longer than the name in column B. But maybe that's OK with your data. You need to evaluate both versions.


Excel 2010
ABCDE
StaplesSears
Office MaxStaples Co
Sears CoJC Penney's
Home DepotLowe's
Jackson'sHome Depot
McDonald's

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A1,IFERROR(FIND(" ",A1)-1,LEN(A1)))=LEFT($B$1:$B$10,IFERROR(FIND(" ",A1)-1,LEN(A1)))))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEFT(A1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","x",1))-1,LEN(A1)))=LEFT($B$1:$B$10,IFERROR(FIND(" ",SUBSTITUTE(A1," ","x",1))-1,LEN(A1)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Yeah, I would evaluate it both ways, or/and maybe even only use the beginning X length as the verification:
Example: "JC Pennys*" wont match "JC Penny's"... but it would if you took the first 5 characters and compared "JC Pe*" to "JC Penny's"
If you think there may be different beginings like "The Home Depot" compared to "Home Depot" you can include an "*" in the beginning and end, and compare both ways (A to B, and B to A)
Just my 2 cents, not sure how much variance there is in your data, but good luck! :)
 
Upvote 0
easy2understandexcel said:
Yeah, I would evaluate it both ways, or/and maybe even only use the beginning X length as the verification:
Example: "JC Pennys*" wont match "JC Penny's"... but it would if you took the first 5 characters and compared "JC Pe*" to "JC Penny's"
If you think there may be different beginings like "The Home Depot" compared to "Home Depot" you can include an "*" in the beginning and end, and compare both ways (A to B, and B to A)
Just my 2 cents, not sure how much variance there is in your data, but good luck! :)
Click to expand...

So how woud I go about comparing the first and last x amount of characters as you stated?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Word to Excel - Not formatting correctly - Can someone help?
Replies
6
Views
267
rgillis32
R
J
  • Solved
Calculate sums based on values
Replies
3
Views
254
NateSC
NateSC
W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
323
waffle
W
S
  • Question Question
Macro to compare two sheets with identical column headers and first column, then showing all table differences in third sheet
Replies
1
Views
177
DanteAmor
DanteAmor
R
  • Solved
Compare similar company names between two tables and return one of the values
Replies
2
Views
997
realtoast
R

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top