• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Auto populate cells from another document table database


I am wondering if this is possible:

I have a huge database of all business subjects (500 MB) in a CSV file, which can easily be saved as an Excel workbook. Data is, for example:
ID     Name              Address            PObox       VAT ID
1       Company 1     Some street      12345       GB12345678
2       Company 2     Some street      12345       GB12345678
3       Company 3     Some street      12345       GB12345678

Open in new window

Now, in my Excel template for sending out offers and proforma invoices, I have 4 fields, each in it's column:

Post Office:

Now, these are TWO excel documents, because it is not whole database to be saved with each output document.

Ok, now I'd like Excel to auto suggest entries from database table as I type into Company field.

Any ideas?
Andrej Pirman
Andrej Pirman
  • 4
  • 2
1 Solution
Hi, Labsy.

A few questions to be going on with, please...

(1) Is the company sorted alphabetically by company name?

(2) How do you feel about using a form for entering the company name? (Cleaner for me and would allow the option of displaying the other database fields as well.)

(3) With that size of a file, I can imagine that there may be thousands of entries which start with the same x characters. That's fine for something like "ACME Building Managers" - you'd just keep typing until you were in the right area of the database ("ACME BU" would probably be sufficient). But what about longer runs - will you always keep typing or would you want to scroll/page through hundreds/thousands of entries?

(4) How many rows in the database?

(5) Roughly how many searches would you do a day?

Andrej PirmanAuthor Commented:
Hi Brian,

thank you for initiative :)
So, let's go:

(1) Can do. I can sort by any column, and move columns left/right to optimize, no problemo.

(2) Form would be good, too. I was thinking about form, too, but lack of knowledge in programming VBS stopped me :)

(3) and (4) File size (database) is about 300.000 companies entries (rows) x 4 data entries for each company (1 row for each company).
What you describe I already have programmed in PHP for my on-line version of workorder for field workers. As you type, top 20 matches are displayed similar to Google suggestion in search field; you may keep typing to narrow results, or pick the result among suggestions with a mouse.

(5) The usage would be for our sales staff, which insist to use Excel for constructing offers for customers. This means, each sales manager would have one pair of TEMPLATE to write the offer, and one DATABASE saved on his/her computer.
They create 10-20 offers a day, so that's the frequency.
Thanks, Labsy - all my questions fully answered!

Your PHP solution of showing the top 20 sounds good. Is that your preferred solution? Another alternative would be to show...
 - The 10, say, entries preceding the first match
 - The first match
 - The 10, say, entries succeeding the first match
...and allow the use to page up and down 10, say, entries.

My searching would be solely based on the first x characters of the Company Name (where x is the number of characters typed by the user). So, in my terms, the "top 20 matches" would simply be the first matching entry plus the next 19 - which is why I would need the data sorted by company name. Is that OK for you?

(BTW, if the first 14 characters had matched, but the 15th didn't then I would continue to display the entries returned for the 14.)

Assuming that all of the above is OK with you, I'd hope to have something to show you by Monday.

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.


Please see attached. Still pretty rough, but let's see how you find it - especially with your full database!

As a starter, watch how the highlighted entry changes as you type in "J" (not case-sensitive), then "U", then "N" - and then backspace through it. When you hit "OK" it displays the currently selected item.

Andrej PirmanAuthor Commented:
Wow, Brian! That's great!

I've tested it on 240.000 rows of real data and it was blazing fast at start...
...but interesting - as I sorted data from A to Z, search slowed down noticeably. If it took before sort less then 0,5 seconds, it pauses now for 2-3 seconds after each keypress.

Maybe the key is in the fact, that before items were sorted, script never displayed more than 1 item matched in search window. But now after items are sorted, script displays all matched items in search window, so it is full of items, which match keyword as I type.

But nevermind...it is excellent!

Just few details to sort out, like:
- how to fill the found & selected row data into 4 fields (instead of into alert window)
- and where to control properties of search window

BTW... you've already earned those points, so I'll deliver them now.
Thanks for the vote of confidence!

as I sorted data from A to Z, search slowed down noticeably
I've been testing it with 320,000 records and, with one exception, it's been great. Under the covers, the macro is simply searching column B for the first record which begins with the search string. If it finds it then it display the found record plus the 12 before it and the 12 after it - so there's only one search.
However, if it doesn't match, then it keeps dropping one character off the search string until eventually it either finds a match or it reaches the start of the file. For example, if I searched for "FredZ" and didn't find it, then the macro would quietly drop off the last character and so look for "Fred". If it found that, fine, otherwise it'd look for "Fre" and so on.
I don't know the spec of your PC's, but with a file that side, you're almost certainly going to see variable response times - as long as Excel/Windows can't keep the entire file constantly in memory you're going to see paging effects. I think it was this rather than the sort that caused the difference you saw.
Another possibility is that you had a long string which stopped matching early in the string - please give me a few examples of the search strings and the actual highlighted name for some of your slow entries.

how to fill the found & selected row data into 4 fields (instead of into alert window)
Yes, the message was just proof of concept. I presume you want the values in four cells - Sheet name and cells, please!

and where to control properties of search window
Sorry, I don't understand - do you mean to change colours, fonts, descriptions, etc?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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