EXCEL - String Compare question

Guys

I have an excel spreadsheet that gathers the same kind of data from different sources. I need to check they match.

In this case I have:

Column A--> Windows Server 2008 R2 Standard Edition
Column B --> Microsoft Windows Server 2008 R2 Standard

What kind of function would confirm they match ?

Thanks!!!!
LVL 2
ferraristaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
So you want to consider those two text strings "the same"?

Is so, you may have to predefined the various strings that are all considered the same.

It's hard to be sure if you try and use some sort of "fuzzy" matching algorithms that exist, since they are only "likely" to be the same.

Another approach would be to just look for the keywords in the strings, like "windows", "server", "2008", "standard" and if all are there consider them the same.

How many different things do you expect to be working with, is this just a "few" or, very large?


»bp
0
ferraristaAuthor Commented:
Hi Bill

thanks for your answer. It would be ok to just match the number, ie "2008". There could be values such as 2003, 2008, 2012 and 2016. Their description be somewhat different, but they will contain these numbers which is ok for me to confirm the match.

Thanks!!
0
Bill PrewCommented:
Also, depending on how you are collecting this data, you might do better looking at less "description" information and go after actual version codes and product numbers from Microsoft.  For example:

https://msdn.microsoft.com/en-us/library/windows/desktop/ms724832(v=vs.85).aspx


»bp
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Bill PrewCommented:
If you just want to do the "year" then that could be a simple regular expression usage.  Are you wanting to do this in a formula on the sheet, of using a small VBA function?


»bp
0
ferraristaAuthor Commented:
Hi Bill. What you say makes sense, but I have no control over the data sources, hence my question to match the portion of string only (ie the numbers).

Is that possible ?

Thanks!!
0
ferraristaAuthor Commented:
A formula would be best, because I have no access to VBA.

Thanks again!
0
Bill PrewCommented:
Would it be reasonable to assume that the number we are looking for in the text is always 20nn, meaning it starts with a space followed by 20, and then that is followed by two more numbers.


»bp
0
ferraristaAuthor Commented:
Yes Bill. Thanks.
0
Bill PrewCommented:
Before I get too far, see if this is along the lines you are thinking...

EE29072382.xlsx


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ferraristaAuthor Commented:
Bill, you're the man! So I need to add threee extra columns, right ?
0
ferraristaAuthor Commented:
Hi Bill. It works perfectly. If you give me a paypal address I will happily pay you a beer!

Thanks again.
0
Bill PrewCommented:
Correct on adding the extra "helper" columns.  And you can always hide the first two if you didn't want them to show on the sheet, etc.

Glad that was helpful.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.