Create Dictionary From Data And Match
Aug 25, 2009
I m creating a dictionary from a set of data that changes, then matching the value to another column and pasting both rows on a new sheet.
I have never worked with dictionaries and I was not able to find much on-line tutorials (help) for this.
I am not even sure if dictionary would be the right tool.
Here's what I am trying to do:
with tow sets of data -- Set 1: columns A-N and Set 2: columns P-AE
-- Go down column M until the last row with data and see if there are any matching values for each value in column M on column AB.
-- For those where a match is found: Copy rows from set 1 column A-N to Sheet "Run" and below copy the rows with the matching value from set 2 columns P-AB.
-- If no match is found, then just copy rows from set 1 on the bottom
-- if there are rows left in set 2 with values not found in set 1, then copy those rows to the bottom of the data in sheet "Run".
View 9 Replies
ADVERTISEMENT
Oct 17, 2013
I have 2 tables, one on the left and another one the right, I want to check existance of ID and date in table in left, if match then plant the details as below. I manage to do the matching, but the result isn't correct.
Matching.jpg
Download workbook here [URL]
VB:
Sub vk()
Dim err As Worksheet: Set err = Sheets("Sheet1")
Dim a As Object: Set a = CreateObject("Scripting.dictionary")
[Code].....
View 9 Replies
View Related
Jan 10, 2014
I have two dictionary objects populated with various keys and items - Dict1 and Dict 2. I've read that the items of a dictionary object can be quite varied, even another dictionary! I know how to add items to keys if those items are strings say, but how to add an entire dictionary (Dict2) as the items of another dictionary (Dict1)?
View 5 Replies
View Related
Feb 5, 2014
I am working on the last step in a very long project, how to filter my data based on keys in a dictionary.
The data I am working with (in the ActiveSheet) has a list of names (in column 6). I want to add these names as keys in the dictionary and the corresponding location (in column 7) as the items. Then, I want to filter the data by each key in the dictionary, copy the filtered data, and paste it into another workbook. I have included the code I have so far. It seems to work exactly how I want as long as I'm not using the dictionary.
View 2 Replies
View Related
Dec 5, 2013
I already did this with excel formulas and it works pretty good. The problem is that when I work with lots (tons) of data, it takes minutes for the formulas to update and do all the needed calculations.
I have been trying to solve this problem using a dictionary object, since it should work faster. However; I don't even know how to start.
My problem (see uploaded Workbook) is this:
I need to calculate daily profits from different departments. For that I need to subtract daily expenses from daily revenues. Unfortunately, the order of appearance of the departments is random. At the end it should look something like this for every day and department:
[Profit].[Dep?] = [TotalRevenue].[Dep?] - [TotalExpenses].[Dep?]
I really don't know what to do with...
Dictionary Object supports below methods :
Add - add key-item in dictionary object
Exists - Check the existence of key in a dictionary object
Items - Get the collection of all items.
Keys - Get the collection of all keys.
Remove - remove particular key-item
RemoveAll - Remove all keys-items
Dictionary Object supports Properties
Count - gets the count of keys
Item - sets or gets the item
Key - sets the key in dictionary object
View 9 Replies
View Related
Mar 23, 2014
I have a spreadsheet with 2 tabs:
AllData:
Name
Data
Read1
[Code]....
View 9 Replies
View Related
Jul 1, 2009
i have a wordlist (dictionary Data) which contains of four Columns ABCD (about 70 000 entries in Column A):
A Words, B Transcription, C Grammar, D Meanings of words in A
Now some entries in Column D contain of reference to other words in Column A. For example: ....
View 6 Replies
View Related
Aug 13, 2014
I have 2 dictionaries; both have a "location" and the "frequency of occurence". The 2nd dictionary locations are related to the first, but are named differently, so I have a range of cells I'm using to define the equivalent locations, ie:
Dict. 2 Location----Dict. 1 Location
Wrapper------------Packaging
Robot----------------Robot Arm
Robot----------------Robot Base
etc..
(It's not a direct 1 for 1)
The program is meant to take both dictionaries, convert the 2nd dictionary keys to the equivalent dictionary 1 keys (based on the cell range data provided) and then combine the values associated with that location and store them in D_Master. D_Master is a copy of my dictionary 1, in which I am also trying to add the values from dictionary 2.
I started by writing the location definitions within the program, i.e.:
[Code]....
which works, but there are several hundred definitions and it becomes less robust, whereas a user could type in a definition within the range of cells and the code below could take care of the rest.
View 5 Replies
View Related
Oct 31, 2009
i have to reverse a very big wordlist containing four coulmns. Column A words, Column B Transcription, Column C Grammar and Column D Meanings. Now i would like to make the meanigs (seperator is ";") to words and words to meaning in another new worksheet added by a macro. For example English-French would then become French-English wordlist.
If the original worksheets name is "x" then a new worksheet should be added with name "Re-x". This new sheet should then contain the new wordlist. For example:
View 12 Replies
View Related
Feb 23, 2010
When we should use scripting dictionary?
Any website recommendation on the net with a good explanation about this, related to use it in excel?
View 9 Replies
View Related
Sep 7, 2006
The comparison between 2 sheets is made in a Class and the Result of the comparison is to be coded in a Module. How the result has to look like is attached as result.xls
I think it has to beginn like this
Sub DisplayResult(ByVal RESULT As Dictionary)
.
.
.
.
End Sub
View 3 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Jun 25, 2008
In cells D2:BM75 I have data about grades either in the format 3a, 3b or as an X which denotes not making progress. Across row1 (D1:BM1)there are column identifiers outlining the subject followed (Maths, English etc). Row 2 onwards contains the pupil name. I need a method of identifying which subject they have an X in for each student. Note that there could be blank cells with no grades present.
Example:
.....................Maths....French...History...Geography
Jon Smith........X..........3b.........X...........5b
Bob Brown.......3c.........X..........4b..........X
The output for Jon Smith (in cell BM2) would be Maths, History
The output for Bob Brown (in cell BM3) would be French, Geography
View 3 Replies
View Related
Nov 9, 2012
I have data in three columns A, B and C. In column A I have values like:
A
1. AOL
2. BA
3. HDP
and now if cell A1 has value AOL then in cell B1 only possible values are:
DD or DP or MP or MR or RE or TP or TT
if cell B1 has value DP then in cell C1 only possible values are:
KO or MA or RS or SA or SE or UM
if cell B1 has value MR then in cell C1 only possible values are:
KO or MA or RS or SA
View 6 Replies
View Related
Jul 29, 2014
I wish to make a transposition using dictionary. I wanted to take a script jindon, but I have difficulties to adapt.
VB:
Option Explicit
Sub test()
Dim a, i As Long, ii As Long, w, rng As Range, cpt As Byte
a = Cells(1).CurrentRegion.Value
[Code] .....
View 9 Replies
View Related
Jul 9, 2014
I have dictionary defined as series of keys and let's say two values:
name1,val1A,val1B
name2,val2A,val2B
...and so on
I would like to define named range from "name" column allowing user to select desired name from combo on another sheet. This is easy
But after that I would like to get val1 and val2 for selected name and show them with some calculation; For example to construct two columns like this: <nameX_selected_from_combo>, (<val1X>+<val2X>)/2
All the problem is how to select values from the same row as name selected in range.
View 2 Replies
View Related
Dec 23, 2007
I have two wordlists (dictionaries) in two sheets and want to make third dictionary out of them in a third sheet.
The first dictionary is Iranian-German and the second one is German-English. Now the macro is supposed to make a third dictionary Iranian-German-English. The new wordlist should contain all words from first list, even if some of them are not translated through macro.
attached excel file to understand the problem better.
Excel file
View 14 Replies
View Related
Oct 31, 2009
I know the Dictionary Object is confined to the Microsoft Word Object structure though can it be used in Excel. If not, is there a suitable replacement.
View 3 Replies
View Related
Nov 12, 2012
In my code below I have verified that the key that I am passing to the item is valid with a debug statement however the item is not being returned.
These two lines are not returning anything. The idea is to pass in the key and get the value.
Code:
Debug.Print dict.Item(Products.Cells(Target.Row, Target.Column - 4))
Products.Cells(Target.Row, Target.Column + 1).Value = dict.Item(Products.Cells(Target.Row, Target.Column - 4))
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ingredients As Worksheet
Dim Products As Worksheet
Set Ingredients = Sheets("Ingredients")
Set Products = Sheets("Products")
' Culculate total cost
[Code] .........
View 1 Replies
View Related
Aug 25, 2008
given a key can i get the position within the dictionary that key/item was found?
so if i go
dic.add "skratch", "more"
dic.add "records", "dude"
how would i obtain the position of "records" within dic? is it possible ?
View 9 Replies
View Related
Mar 17, 2014
I have a workbook that requires the user to enter data for an appointment calendar.I need a formula that will list all the appointmets that are entered for a seleted date and time.
View 11 Replies
View Related
Jul 13, 2013
I have a VBA code which checks the value of two cells in "Sheet2", and when these values are in the fourth column of "Sheet4", then dictionary is saved and after that VBA delete Entire Rows in "Sheet4" which consists these values.
Please find the code below:
VB:
Sub dictionary1()
Application.ScreenUpdating = False
Dim dico1 As Scripting.Dictionary
Set dico1 = New Dictionary
[Code] .....
Now I wonder how to add more conditions like for example: "Delete all rows in "Sheet4" which have for instance in the 6th column text "Hello" AND which have in the 7th column value greater than 10". How can I change the existing code?
This is of course just an example, but the underlying question is how can I add to these dictionaries more conditions. Of course, we can still use "Sheet2" to add some value which we want dictionary to store.
I want to use for this only dictionaries because spreadsheet is large and filtering doesn't work at all...
View 7 Replies
View Related
Mar 17, 2014
create a macro that will generate 2 words from the dictionary. The first word needs to start with the letter B and the second word must start with the letter T.
View 1 Replies
View Related
Mar 9, 2009
I have created a dictionary with technical terminology. It is in excel format with about 10,000 entries. The format of my dictionary ("Sheet1") is in three columns:
<vocabulary term> <pronunciation> <definition>
Now, I want to create vocabulary lists using my dictionary. The newly typed up vocabulary list (which only has the term, but not pronunciation or definition), will be copy and pasted into "Sheet2".
The "Sheet2" typed up vocabulary list is in the format:
<vocabulary term 1>
<vocabulary term 2>
etc...
I want to run a macro or script to dictionary terms (including pronunciation and definition) from "Sheet1" and put them into my vocabulary list "Sheet2".
The desired output list in "Sheet2" is: .....
View 11 Replies
View Related
Nov 10, 2009
The code works well. Though there are a few that that i dont understand.
The same range in used for the listbox and the combobox. Though i cannot send the Variable Rng through the scripting dictonary it seams that this function is looking at the range in a diffrent manner to a standard range.
so you see i have another variable datarng for use in the function though if i use set to set the variable as the range it wont like that either.
View 8 Replies
View Related
Oct 1, 2013
I'm trying to build a dictionary of abstract words. It seems that all abstract words are built from less than 30 other indefinable words. I need a macro that can generate column H. I would prefer a macro because ultimately i will probably have a database of about 300 words. What I need is a macro that will go down column G, see the word "after" then find that word in column e and the corresponding number in column f, then write that number in column H. Every word that occurs in a row in column g, should ultimately be converted into a number in column h.
View 6 Replies
View Related
Dec 31, 2008
I have data in a dictionary object and need to load it into a two column listbox. The VBA help says you can load data into a multicolumn listbox from a 2D array. So my question is how can I extract the data from a dictionary object directly into a 2D array.
The dictionary object stores data in key and item pairs. So the 2D array would have one dimension for the keys and the other for the items. I want to do this without having to extract the items and keys into separate 1D arrays and then loop through them to build the 2D array.
View 9 Replies
View Related
Jan 20, 2012
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
View 2 Replies
View Related
Jun 27, 2012
I am using a dictionary object from the MS Scripting Runtime library to store a series of arrays and perform operations on the array cells as necessary. There is a for loop to go through the process of creating all of these entries. My issue is that when using the .exists property, it is returning a True even before the item has been added. Closer debugging indicates that the key is being added to the dictionary at the beginning of the for loop, even though no .add command is used and will not be used until the end of the loop. The result is that the values in the arrays do not totalize as intended.
Code for the dictionary build has been included below. I have attached the Excel file which contains the appropriate module which calls that function
VB:
Option Explicit[ATTACH]46705[/ATTACH]
Function DictAppTable(Loop_Range As Integer, Key_Range As Range, Dim_1 As Range, Dim_2 As Range)
Dim DictTable As Dictionary
Dim AppElement(0 To 0, 0 To 1) As Variant
Dim iD As Integer
Dim strAppID As String
[Code].... the previous post, as the pared down excel file which I posted had a slight error which prevented it from running
View 2 Replies
View Related
Mar 30, 2012
What I'm trying to do:
1) I have data that has a header row of 6-10 values (created, action, type, id, ...). The rows are unique records.
Click here to see the data
2) I originally wanted to read the data into one array (DataArray) and the header into another array (KeyArray). Then I would add additional unique information about each record (additional columns of info) into the DataArray.
3) I stumbled upon Dictionaries as a way of storing key/item pairs, which I thought could apply to each record since the headers are all unique and would be a way of storing key/items without needing to:
a) Know the ordering (if I used arrays I had to know the upper bound of the array to insert a column
b) Look up which what position in the array mapped to which column header
c) Resize the array every time I wanted to insert a new column of information on all the records
4) So now that I know about a dictionary object, I believe I need to create one Dictionary object for EACH record (row) of my data and then store those Dictionary objects within a one dimensional array such that each element of the array is a Dictionary.
5) This way I can iterate through each element of the array to access the dictionary inside and perform calculations on each record, depending on which key/item I needed to work with at a later point
Questions for Dictionary experts:
1) Is my approach sound?
2) What is the syntax for putting the dictionary of one row's worth of data into an element of a one-dimensional array?
View 8 Replies
View Related
