Dictionary Within Array Of Dictionaries - Pulling Out Values
Apr 20, 2012
I would like to get data onto my worksheet that looks something like what I have below. I'm pretty set on using Dictionaries to get the data into this format
(row 1 reads): header 1, header 2, header 3
(row 2 reads): value 1, value 2, value 3
(row 3 reads): value 1, value 2, {"property1": property1, "property2": property2, "property3": property3}
I'm close to doing so but having issues with my syntax.
I can refer to:
arrExport(1)("key3") to get the value in key3
but I cannot refer to:
arrExport(1)("key4")
Because key4 is what contains another dictionary.
Code:
Sub DictWithinDict()
Dim dict As dictionary, dictValue As dictionary
Dim arrExport() As dictionary
Dim i As Integer
ReDim arrExport(1 To 10) 'real data will be in thousands
[Code] .....
View 3 Replies
ADVERTISEMENT
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
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
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
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
Nov 18, 2012
I have a large dataset that I need to push into a dictionary, into that dictionary I also have to create other dictionaries and this can go on until I fill the entire dataset into the dictionary. The problem is that I do not know beforehand what the key names will be so I need to find a way to get the key names.
I have the following test code:
Code:
Dim player As New Dictionary
player.Add "val1", "hello123"
player.Add "val2", "piet123"
[Code]....
how to get reference the key in a nested dictionary not using the keyname?
View 2 Replies
View Related
Nov 12, 2007
HOW TO pull the unique values out of a column of entries and put them into another column.
For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
View 14 Replies
View Related
May 6, 2008
Got a requet from a client that we can't do with out accounting software. Basically I have a folder of all the clients indivisual invoices for 2008. We want to put together a report that will include the employee name, invoice date, and amount. But the only way to get this right now that I see is to sift though the hundreds of workbooks we have for them, one by one. Is there something that can be set up to pull 3, 4, or 5 of the same fields say cell A1, B3, C3, and D6 our of every workbook in a single folder on our network?
View 12 Replies
View Related
Jun 12, 2014
I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.
I've attached an example spreadsheet for reference. Basically, I need two formulas:
1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).
My actual data file has 80+ senders and 100+ receivers.
View 6 Replies
View Related
Feb 20, 2014
I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:
[URL]
As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:
Code:
'Import from www.basketball-reference.com
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _
, Destination:=Range("$A$1"))
.Name = "dailyleaders.cgi?month=02&day=19&year=2014"
View 2 Replies
View Related
Oct 2, 2008
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Oct 22, 2013
Question contained within code:
Code:
Dim MyArray(6, 1) As String
Dim i As Integer
'' Array List 1
[Code]....
'If lbxLI = any list index from Array List 1 then use the value stored in Array List 2 to give Listbox 2 its rowsource.
'For example; ListIndex "0" would produce a row source "_0" for listbox 2. Else if ListIndex "1" is selected, produce row source "_1" for listbox 2.
' I would like to achieve this from one "If" statement. I'm guessing it must be possible using a loop, I just can't think how despite a lot of messing about with code.
View 1 Replies
View Related
Dec 11, 2008
I have a couple of columns that look like this in basic form:
Column A Column B
A 1
B 1
C 0
D 2
E 1
F 0
G 2
What I want is in a separate worksheet for it to pull the names (A, B, E) that have 1s next to them in column B and put them in a list.
I could do this kinda manually, but how can I create a nice list in a separate column on a separate worksheet just of the names (column A values) where there is a 1 in column B? All I can think to do is vlookup the data and put blanks where its not equal to 1 and then manually delete out the other rows.
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
Aug 3, 2006
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO()
Dim TNs As Long
Dim i As Long
Dim TempArray() As String
Dim TRCO As String
Dim CD03 As String
Dim ASOC As Range
TRCO = "TRCO"
CD03 = "CD034DF1"
'Filter TN's
Columns("B:B").EntireColumn.Insert
Set tempRange = Range("A6", Range("A65000").End(xlUp))
With tempRange
. AdvancedFilter _...................
View 2 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
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
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
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
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