Question partially compare two columns in excel sheet

[diliptimblo]

I want to partially compare two columns in excel sheets and highlight the cells?

 

[diliptimblo]

Pull the Matching Data (Partial)- in this, I want to compare column A and Column D and highlight the cell which contains partial match

 

[diliptimblo]

Did you mean THIS??
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwjY3qqqgYH2AhVlg_0HHZKtBMgQFnoECAQQAw&url=https://trumpexcel.com/compare-two-columns/&usg=AOvVaw1jwO1BdNpFvb88IUiscMf8

Follow the Instructions In the above link

Pull the Matching Data (Partial)- in this, I want to compare column A and Column D and highlight the cell which contains a partial match

 

[Alabalcho]

Create a new column, with simple compare formula

=if(a1=b1,1,0)

then create conditional format for original columns based on that new column' values.

 

[kanewolf]

Pull the Matching Data (Partial)- in this, I want to compare column A and Column D and highlight the cell which contains partial match

What does "partial" mean? A portion of text?

 

[diliptimblo]

yes

 

[Alabalcho]

Can you define precise criteria for a "partial match"?

• "abc" vs "123abc456"
• "abc" vs "abc123456"
• "abc" vs "123456abc"
• "abc" vs "123ab456c"

 

[kanewolf]

Can you define precise criteria for a "partial match"?

• "abc" vs "123abc456"
• "abc" vs "abc123456"
• "abc" vs "123456abc"
• "abc" vs "123ab456c"

And is there consistency. If this is rows 1 through 4 and you want to match all of them, you are going to have problems.

 

[diliptimblo]

Just to give you all a clear idea,
Column A with the following values
Apple
Amazon Inc
Alphabet
Microsoft Inc
Bank of America

Column B with the following values
Amazon.com
Apple Inc
Samsung
Alphabet
Facebook

If I compare these two columns Apple, Amazon, Alphabet are common. So I want to highlight Apple, Amazon, Alphabet in column A

 

[Nigel Spike]

It appears as if you want to compare what is written to the left of a dot or a space.
However "Bank of America" contains two spaces. Do you you want it to match for example Bank.com and/or Bank of Arkansas?

The idea is not clear yet.

 

[Ralston18]

Use Microsoft Access.

Likely that you can link into the original spreadsheet columns and query for matches.

https://support.microsoft.com/en-us...ing-data-16f301ac-40c1-43bc-80db-263f9a51eb4f

Excel does have a number of LOOKUP functions but that (depending on details) could prove cumbersome.

And per @Nigel Spike add some detail regarding the requirements.

 

[Alabalcho]

And to make it even more confusing:

• which of the columns is "fixed"?
• as you said, a "match" is "is the value in column A part of any value in column B";

To the best of my knowledge, Excel' lookup functions do exact match only. You will probably have to write your own lookup function with exact "mactch" you need, which means time to learn VBA.

 

[diliptimblo]

It appears as if you want to compare what is written to the left of a dot or a space.
However "Bank of America" contains two spaces. Do you you want it to match for example Bank.com and/or Bank of Arkansas?

The idea is not clear yet.

Yes, I want it to match for example Bank.com and/or Bank of Arkansas?

 

[USAFRet]

Yes, I want it to match for example Bank.com and/or Bank of Arkansas?

Partial matches...

Bank.com
BankOfAmerica
Bank Of America
BanThisWebsite

Would those 4 count as a match?

What if we add Barter.com

 

[Deleted member 14196]

As mentioned above you will most likely need Visual Basic for Applications or VBA

 

[diliptimblo]

Partial matches...

Bank.com
BankOfAmerica
Bank Of America
BanThisWebsite

Would those 4 count as a match?

What if we add Barter.com

It should not count Barter.com

 

[USAFRet]

It should not count Barter.com

Why not?

You need to define how many consecutive characters constitute a match.