Solved

Excel Compare Columns with Wildcards

Posted on 2011-03-16
3
526 Views
Last Modified: 2012-05-11
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
Comment
Question by:BusinessSolutions
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 50 total points
ID: 35149488
Select column A and add a conditional formatting formula like:

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

Thomas
0
 

Author Comment

by:BusinessSolutions
ID: 35149709
Does this only compare numbers? I have some entries which have a lettered account number.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35151518
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

Want to promote your upcoming event?

Attending an event? Speaking at a conference? Or exhibiting at a trade show? Easily inform your contacts by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now