Solved

Auto populate cells from another document table database

Posted on 2013-01-18
6
311 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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