Sonny G
asked on
VBA Dictionary created from an Excel spreadsheet.
After I identify a certain row in a spreadsheet, I would like to copy the value in column "S" from that row as the key and copy the value in Column "T" from that row as data into a scripting dictionary that I can later print.
I am only interested in creating a dictionary entry if column "S" has the word "Note" in it.
Both the key and the data are strings.
There will be duplicated keys and I would like to ignore duplicates and have them listed in the dictionary only once..
After assembling this dictionary, I would like to sort it by the key before printing.
As an overview, I will print this dictionary in its entirety at the end of a spreadsheet (which I can do). I would like to use VBA code for this.
I am only interested in creating a dictionary entry if column "S" has the word "Note" in it.
Both the key and the data are strings.
There will be duplicated keys and I would like to ignore duplicates and have them listed in the dictionary only once..
After assembling this dictionary, I would like to sort it by the key before printing.
As an overview, I will print this dictionary in its entirety at the end of a spreadsheet (which I can do). I would like to use VBA code for this.
Martin Liss
Do you have a sample workbook you could share?
ASKER
Hi Martin!
I hope that all is well with you. Thanks for replying.
I cannot share company information/assets. Is it possible that you can help with the code based upon what I have described?
I hope that all is well with you. Thanks for replying.
I cannot share company information/assets. Is it possible that you can help with the code based upon what I have described?
You're welcome and I have the same hopes for you.
I don't need a lot of data so couldn't you copy some rows from your production workbook to a new workbook, sanitize those rows and post that sample?
I don't need a lot of data so couldn't you copy some rows from your production workbook to a new workbook, sanitize those rows and post that sample?
Lenny
Will there me multiple items of data for each key?
If so, how would you want to store that data?
P.S. It's not possible to have duplicate keys in a dictionary, that's kind of onee of the reasons to use one.:)
Will there me multiple items of data for each key?
If so, how would you want to store that data?
P.S. It's not possible to have duplicate keys in a dictionary, that's kind of onee of the reasons to use one.:)
ASKER
Only a key and a description. The key is in column "S" and the data is in column "T".
The ultimate work product will be a spreadsheet with a bill of materials for several projects to be given to a particular vendor. The part number can be repeated in the work product spreadsheet because different projects will have some of the same parts. The part number description has attributes than be as short as 50 characters and as many as a few hundred characters.
The work product spreadsheet delivered to the vendor will have the part number and they key (e.g. "See Note 3"). Since the description of some part numbers is so long, we decided to say "See Note 3" rather than print a long spreadsheet. The "Note 3" will then print on the bottom of the spreadsheet only once and perhaps refer to dozens of part numbers that are of the same key. The dictionary will the print the Note Number (key) and then the long description as a reference to a note for that item only once, rather than repeat a long description on every line.
The ultimate work product will be a spreadsheet with a bill of materials for several projects to be given to a particular vendor. The part number can be repeated in the work product spreadsheet because different projects will have some of the same parts. The part number description has attributes than be as short as 50 characters and as many as a few hundred characters.
The work product spreadsheet delivered to the vendor will have the part number and they key (e.g. "See Note 3"). Since the description of some part numbers is so long, we decided to say "See Note 3" rather than print a long spreadsheet. The "Note 3" will then print on the bottom of the spreadsheet only once and perhaps refer to dozens of part numbers that are of the same key. The dictionary will the print the Note Number (key) and then the long description as a reference to a note for that item only once, rather than repeat a long description on every line.
ASKER
Hi Guys,
Attached is a workbook with two worksheets. One is the vendor work order and the other is the database. The data is masked but the format is what I am looking at.
In the database, if the number of minor items is few and takes a line of a cell, then there is no note number. If the number of minor items is extensive taking a few wrapped lines then we reference a note number.
The goal is to have a line per work deliverable. The deliverables are more than shown on the sample. A vendor could have 100 or more projects.sample.xlsx
Attached is a workbook with two worksheets. One is the vendor work order and the other is the database. The data is masked but the format is what I am looking at.
In the database, if the number of minor items is few and takes a line of a cell, then there is no note number. If the number of minor items is extensive taking a few wrapped lines then we reference a note number.
The goal is to have a line per work deliverable. The deliverables are more than shown on the sample. A vendor could have 100 or more projects.sample.xlsx
So what should happen when there is a duplicate key?
For example, in the uploaded file on the database sheet 'See Note 56' is repeated numerous times - which value should be stored in the dictionary for that key?
For example, in the uploaded file on the database sheet 'See Note 56' is repeated numerous times - which value should be stored in the dictionary for that key?
For anyone tackling this, here's my contribution. Here's a Dictionary Sort function I have used in the past to do that tricky part of the process...
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery - Sorting By Keys
»bp
Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery - Sorting By Keys
»bp
ASKER
Norie - The note 56 is a mistake. It was a simple sloppy copy and paste. The note number never repeats in the database. It should have been See Note 56, See Note 57, See Note 58, etc.
<slapping self>
<slapping self>
I'm dropping out of this.
Norie, you're working, yes?
I don't want to "waste" time duplicating work if you are on it, it's into the evening here now 🙂.
UPDATED: Although now I see, it's 5 hours later where you are...
»bp
I don't want to "waste" time duplicating work if you are on it, it's into the evening here now 🙂.
UPDATED: Although now I see, it's 5 hours later where you are...
»bp
ASKER
I appreciate everyone's help. I am willing to stay up and wait for a great solution.
Lenny,
I on to some reading tonight, but will check back on this tomorrow and if you haven't gotten help will try and put something together.
But what you need at this point isn't too bad, just load the keys and values from cols S and T into a dictionary (eliminating duplicates), and sort it. Should be experts that can knock that out pretty easy.
»bp
I on to some reading tonight, but will check back on this tomorrow and if you haven't gotten help will try and put something together.
But what you need at this point isn't too bad, just load the keys and values from cols S and T into a dictionary (eliminating duplicates), and sort it. Should be experts that can knock that out pretty easy.
»bp
ASKER
Thanks, Bill. I am stuck on this. You have taught me so much over the past few months and your solutions were always spot-on.
I have a Monday deadline on this project and have not found anything on the Internet/YouTube to solve this, so I will be working the weekend. The sorting issue is the least of my needs on this request. If the notes are printed out of order, that will be manageable because the dictionary is used only once per work order and then trashed.
Building the dictionary is the challenge.
The rest of the stuff that I have to get done, I know how to do.
I have a Monday deadline on this project and have not found anything on the Internet/YouTube to solve this, so I will be working the weekend. The sorting issue is the least of my needs on this request. If the notes are printed out of order, that will be manageable because the dictionary is used only once per work order and then trashed.
Building the dictionary is the challenge.
The rest of the stuff that I have to get done, I know how to do.
Dictionaries are actually pretty easy, just a little syntax you have to get down, but the concept and usage isn't bad at all. It's just a table of key / value pairs, and you can access the entries in the dictionary by that key.
Let me see if I can find a decent page or two that might give you more background.
»bp
Let me see if I can find a decent page or two that might give you more background.
»bp
Oh, and here's an EE article by Patrick Mathews, very bright guy, although less active these days. I didn't read the whole thing but I think it also covers a lot of the basics, plus some detail stuff.
Using the Dictionary Class in VBA | Experts Exchange
»bp
Using the Dictionary Class in VBA | Experts Exchange
»bp
ASKER
Thanks, Bill !!!!
And a real terse "cheat sheet" that is easy to digest. Okay, that's enough of these...
Visual Basic/Dictionaries - Wikibooks, open books for an open world
»bp
Visual Basic/Dictionaries - Wikibooks, open books for an open world
»bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, Norie! I'll give this a go and I'll get back to you with the result or any further questions.
You are wonderful!!!
You are wonderful!!!
ASKER
Attached is the final solution. FYIDictionarySolution.txt
Interesting, that looks quite a bit different. But happy you got a solution that works for you.
No need to sort either?
»bp
No need to sort either?
»bp
Lenny
I'm a little confused.
What is the code in 'DictionarySolution.txt' meant to do?
I'm a little confused.
What is the code in 'DictionarySolution.txt' meant to do?