Solved

Approximate search in text data (Excel VBA, Excel SQL)

Posted on 2013-01-27
8
770 Views
Last Modified: 2013-01-28
Hello, dear members and experts!

There are good (efficient) solutions for Approximate text seach in data (for Excel VBA, Excel with SQL-queries)?

The main task is: finding from external database (Excel's file) data corresponding to data in specified collumn in main Excel's sheet.

Second task: include approximate search into custom made improved_VLOOKUP function –http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28009228.html

Thanks in advance!
0
Comment
Question by:Last_Free_Man
  • 3
  • 3
  • 2
8 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38824075
Last_Free_Man,

Could you be more specific about what approximate search means to you, please? Links to existing good solutions would be good!

Thanks,
Brian.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38824198
Agreed with Brian: we need clarification on what an approximate match is.

Taking my approach from the last question, suppose you need "the Order Number for the 7th item from the top where the Manager's name starts with 'J'."

In that case, you could use this array formula:

{=INDEX($C$1:$C$21,SMALL(IF(LEFT($A$1:$A$21,1)="J",ROW($A$1:$A$21),""),7),1)}
0
 

Author Comment

by:Last_Free_Man
ID: 38824729
I will try to explain my programmer's tasks.

I have two tables.

Table_A (XLSX file) containts column with projects names (text string with length equal 255 characters).
This data based on users input. Because of human factor projects names often have inaccuracy: double spaces, lack of spaces, mixed caps and small letters, lack of pointings and etc.

Table_B (XLSX file) containts column with project names too. But this field can have length more as 255 characters (and sometimes with NewLine symbols). Usually data in this column is more accurate than in Table_B but this is also result of users input and edit.

The task is find data in Table_B correspond to data in Table_A (with approximated search)

I need in solution based on VBA (as part of many checking and fixing subprograms).
SQL-queries (with ADO connection) are also good tool (I use it now in this current checking project).

The main plan is: use RegExp for throw off any characters in string except numbers and letters and then compare string with simplification.

Do you have any different ideas? - Good!

P.S. I have good programmers background: Perl, MySql, a little bit of JavaScript. But VBA and ADO is new languages and technologies for me. (Sorry! I'm newbe in English too!)
Help me please!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38824746
Thanks,  Last_Free_Man.

You've got matthewspatrick involved and as he is exactly the right expert for this, I'm going to quietly leave!

Regards,
Brian.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38825168
Sample file(s) would be helpful :)
0
 

Author Comment

by:Last_Free_Man
ID: 38828114
Example attached.

It contains cyrillic symbols.
If you can't read then please write here and I will make transliteration.

Thanks in advance!
2013-01-28-Example-for-EE.xlsm
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38828564
I'm usually game for RegExp work; indeed, I wrote one of the definitive articles on using RegExp with Excel: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

However, doing that with cyrillic characters is going to be over my head.

I think you should use Request Attention to ask the Mods to add more zones (Regular Expressions, Perl, and JavaScript make sense), and send out alerts for Designated Experts.

Patrick
0
 

Author Comment

by:Last_Free_Man
ID: 38829924
Dear Patrick!

Yours arcticle is brilliant!

Thank you very much!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

10 Experts available now in Live!

Get 1:1 Help Now