Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Auto populate cells from another document table database

Posted on 2013-01-18
Medium Priority
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
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
  • 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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 26

Accepted Solution

redmondb earned 2000 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 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.
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

618 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