[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Excel Compare Columns with Wildcards

Posted on 2011-03-16
Medium Priority
564 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
Question by:BusinessSolutions
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
3 Comments

LVL 39

Accepted Solution

nutsch earned 200 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

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

LVL 39

Expert Comment

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

Question has a verified solution.

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

Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
###### Suggested Courses
Course of the Month13 days, 21 hours left to enroll

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

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