1. S

    Dictionary with array as the value

    Hi, I have been battling to understand why an array is not being updated where the array is in place of a key/value pair. I have the following in VBA: Dim testDict As Object Dim testKey As String Dim arr(1) As Double Set testDict = CreateObject("Scripting.Dictionary") testKey = "TestKey" arr(0)...
  2. D

    Comparing Lists for new values - Dictionary or Array?

    Hi folks. I have some working code here but it is very slow. Could anyone suggest a faster alternative? I did try using the dictionary but eventually gave up. It was the passing of the dictionary to and from the sub while also passing the sheet name that was causing the problems. I think perhaps...
  3. J

    Creating a dictionary based on some input format?

    To all As part of macro (to post process data), I am exploring the ideas of getting rid of a vlookup() creation by something that could potentially be more flexible (long term) With the following data held in 1 tab What would be the best way of creating a dictionary which would look like...
  4. A

    Retrieve multiple values using VBA from dictionary

    Hi, Long time lurker, first time poster and VBA newb so bear with me. I am looking for a way to automate some reports that are currently done manually. Sheet1 contains a list of WO numbers in Column C. Sheet2 contains a list of potential matching WO numbers in Column A. Looking to take the WO...
  5. D

    Filtering data using a dictionary

    Hi, I would like to create a macro that will filter the data, but the problem is, I don't know exactly what the data will be in the file. For this purpose, I used a dictionary that goes through a given column and saves the keys that I want to filter later. Unfortunately, I don't know how to get...
  6. R

    Compare if exact match return the ID adjacent to it

    Hi, I've got two workbooks ExtOrgsList.xlsx contains an ID of a Customer. The ID is in Column A and the Customer name is in Column B. Destination.csv has column AN which contains the Customer names. The code below works but if there's any deviation in the name it returns the partial match...
  7. D

    VBA Dictionary difference

    Hello, I am seeing two different things with dictionaries and was wondering what is the difference between the two and the benefit of using one over the other. I am seeing Dim dict as new dictionary and Dim dict as scripting.dictionary what is the difference between new dictionary and...
  8. D

    vba extract data from text file

    Hello, I am wondering if there is a way to select a text file and loop through the text file line by line. each line is similar to this structure "FORM 1","1","0","0","NAME","Diego","O" "FORM 1","2","0","0","NAME","Pedro","O" etc. . . and put it into a dictionary of sorts to store the...
  9. F

    Getting only the values base on a list

    Using this code below gives me a result of a unique customer codes base on Calculation sheet. However, I want to get my result base on the list that I have in Solution Sheet. Also want to run the macro in button inside Solution sheet. Any help will be appreciated. Calculation Sheet Solution...
  10. F

    Countif and SUMIF using dictionary from another sheet

    I'm trying to use dictionary for 3 different columns with COUNTIF and SUMIF getting the value from another sheet. I'm having a hard time getting the right code to get the value from another sheet, below is my currently using code. Any help will do thanks. Transact Sheet Invoice Sheet Sub...
  11. C

    VBA multi value Dictionary

    I am trying to build a multi value dictionary from a sheet of data that has 4 different columns of data, where the first column would be the key and the 3 next columns would be the items I keep getting a compile error: Method or Data member not found. Please help
  12. C

    adding items to existing database

    Hi just checking If anybody can help be with this as I have a long way to go I have a simple VBA code that lets you import data from another workbook into active workbook by using the file open dialog. currently the code just clears the sheet and imports all the data into active workbook. I...
  13. R


    Dear All master, I want to make transpose with vba code dictionary and array because the number of rows or records is 10,000. The sheet name is "MASTER" and is it possible to directly transpose on the same sheet and create a table directly? this is original data...
  14. D

    Collecting data with a dictionary vba

    Hello, I was told to collect before worrying about printing and doing so using a dictionary and a unique key The unique key is something in the outer dictionary is what i was told too But i am not sure what that means, or the creation of a dictionary. i am new to VBA and trying to create...
  15. H

    Compare Dataset Counts Using Multiple Parameters

    Hi everyone, I'm migrating data from a legacy source information system to a new information system and want to confirm a variety of counts of values from the source data against the counts of the same values from the migrated data to ensure the counts match as a way to verify the records...
  16. C

    How to use dictionary method instead countif?

    I want to use vba dictionary method to calculate over 100k rows instead of in worksheet countif function to reduce time of calculation but I could not find how to convert my formula which shown in below. Range("AG2").Formula = "=COUNTIF($AF$2:$AF100000,"YES" & X2 & Z2 & "TRUE")"...
  17. J

    Compiling and accessing nested collection within a nested dictionary

    Hi, First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to. What I want to do: Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the...
  18. D

    Dictionary, Collection or Array?

    i have the following excel sheet and need to reformat the data from a vertical view to a horizontal one while lining up the data in the appropriate columns. the vertical column is labeled Key and Value ("A:B") the reformatted output is labeled to the right called Key, followed up by the keys...
  19. DataBlake

    Using inputbox to check dictionary

    Sub findQTY() Dim pnum As Variant pnum = InputBox("Please Type in the part number") If dicQTY.exists(pnum) Then MsgBox dicQTY(pnum) Else MsgBox "No quantity available for this part number" End If End Sub the objective is to have the user enter a part number and it will either return the...
  20. DataBlake

    Personal Workbook Macro Punlic Dictionary Set Up [vba]

    I want to create some dictionaries that i can access in any workbook. I.E a list of colors as the key and a list of shortened versions of that color. Gloss Black White Trim = key Black = Value i have a sheet with these values that i would like to establish as a disctionary A: will this work if...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back