Solved

Auto populate cells from another document table database

Posted on 2013-01-18
6
285 Views
Last Modified: 2013-01-21
Hi,

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:

Company:
Address:
Post Office:
VAT ID:

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

Expert Comment

by:redmondb
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?

Thanks,
Brian.
0
 
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.
0
 
LVL 26

Expert Comment

by:redmondb
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.

Regards,
Brian.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38803171
Labsy,

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.

Regards,
Brian.Search-Form.xlsm
0
 
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.
0
 
LVL 26

Expert Comment

by:redmondb
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?

Thanks,
Brian.
0

Featured Post

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

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

747 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

12 Experts available now in Live!

Get 1:1 Help Now