Auto populate cells from another document table database

Posted on 2013-01-18
Last Modified: 2013-01-21

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?
Question by:Andrej Pirman
  • 4
  • 2
LVL 26

Expert Comment

ID: 38795727
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?

LVL 18

Author Comment

by:Andrej Pirman
ID: 38796194
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.
LVL 26

Expert Comment

ID: 38796395
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.

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

LVL 26

Accepted Solution

redmondb earned 500 total points
ID: 38803171

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.

LVL 18

Author Comment

by:Andrej Pirman
ID: 38803335
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 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.
LVL 26

Expert Comment

ID: 38803439
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?


Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

786 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