Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Excel Compare Columns with Wildcards

Hi,

Goal: To create a spreadsheet which compares 2 columns and highlights the entries which appear in both columns, but i need to use wildcards.

Basically, I have 2 columns, 1 contains numbers followed by a name, and the other column just contains numbers. So i want to be able to highlight the entries that appear in both columns but only using the numbers as the comparison.

E.g.
Below is my test data, So i want to compare the 2 columns using the numbers

So in my example 11111 Joe Bloggs LTD would be highlighted because the numbers match an entry in column B. Where as 125445 Free LTD would NOT be highlighted because there isnt a number match in column B.

I figured i could use some kind of =COUNTIF statement but having no joy with the wildcards thus far.

If there a formula i can use for this as i have about 5000 records to compare?


 Book2.xls
0
BusinessSolutions
Asked:
BusinessSolutions
  • 2
1 Solution
 
nutschCommented:
Select column A and add a conditional formatting formula like:

=COUNTIF(B:B,--LEFT(A1,FIND(" ",A1)))>0

Thomas
0
 
BusinessSolutionsAuthor Commented:
Does this only compare numbers? I have some entries which have a lettered account number.
0
 
nutschCommented:
Yes, since the -- tries to convert account number to numbers

You can try
=COUNTIF(B:B,IFERROR(--LEFT(A1,FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)))

if you have mixed acct numbers

T
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now