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].....
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] .........
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 ?
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".
numberdesc
1_______yellow
1_______yellow
1_______blue
2_______purple
2_______purple
3_______green
3_______orange
4_______black
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then
Range("C2:C9").Sheets("Sheet1") = "x"
End If
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 Relatedhow I can match an item in a row with another row?
I have a row of data A2:J2. A persons name could be in any column in that row.
I have another two rows of data where the peoples name and department name appear Where L2:L7 = peoples names
where M2:M7 = peoples department
I want to match a name in A2:J2 with one in L2:L7 and if matched put the department name in K2. Then obiviously i will copy the formular down column K.
I have three worksheets the first one called Econ the second one called CheckLogs and the third is a customer sheet. I am needing to match up a cell from the Econ sheet with a cell from the Checklogs sheet and then it matches a cell from the customer sheet with a cell from the checklogs to determine what customer sheet. My problem is how do I loop through the customer code cell until it finds the correct cell.
=IF(B1=CheckLogs!B2,INDEX(CheckLogs!$F$2:$F$4493,MATCH(Econ!G2,CheckLogs!$C$2:$C$4493,0),MATCH($B$1,CheckLogs!$B2:B4493,0)), "")
so in the formula "B1" is located on the customer sheet and is the customer code field, "CheckLogs!B2" is on the CheckLogs sheet and is the customer code field. I'm just not sure how you loop if the customer code in B1 doesn't equal the customer code in CheckLogs!B2 and run through the formula again.
I am trying to write one Excel formula that can accomplish the following: Review a Discount Sheet to determine whether a Class Name should be purchased based on its current discount compared to my Target Discount for that Class Name. The majority of the time, weekly discount changes occur in 5-10% increments, but occasionally they will occur in increments of 20% or more. If a discount percentage increases by 20% or more and surpasses my Target Discount, the current method I am using to automate this process does not work.
Example: Last week's discount in cell G2 is 25%, and this week's discount in cell H2 is 40%. The Target Discount for that class is 30%, so under my current system I would not request that Class Name for purchasing because the current discount does not exactly equal my Target Discount, even though the previous week's discount did not exceed my Target Discount and now I should purchase that Class Name because the discount has gone from not meeting to exceeding my Target Discount.
INDEX MATCH used to retrieve current discount:
=INDEX('Discount Sheet'!$H$2:$H$4910,MATCH(A3,'Discount Sheet'!$F$2:$F$4910,0))
Validity Test in Cell F2 to compare Current Discount to Target Discount:
=E2=C2
NOTE: The above validity test could include a >= to capture Current Discounts that have exceeded the Target Discount; however, over time all Current Discounts will exceed the Target Discount. It is only when last week's discount did not exceed the Target Discount and now the Current Discount does exceed the Target Discount should a class be purchased.
The final result of this formula should be "TRUE' or "FALSE" without having to cut and paste any data for additional validity comparisons.
I have multiple sheets within one Excel file. Each sheet is structured the same way. Column B lists Client's last names, which differ from sheet to sheet, though some clients may be repeated in several tabs. I have a column K which displays either the number 1 or 0. This is consistent between sheets.
I have created a formula that will look at the last name in a specific cell in column B of sheet 2 (FY 07) and search sheet 1 (FY 06) column B for the same last name.
=IF(MATCH($B5,'FY 06'!$B$5:$B$24,0),1,0)
If the same last name exists and if column K of sheet 2 and sheet 1 display a "1", I want it to place a 0 in the formula cell.
So, if there is a client that is in both sheets and has a "1" in both columns K of each sheet, I want the formula to let me know.
I amtrying to find a way to nest a formula in the Match function.
How can i make the following formula more dynamic so that I do not specify the colum "AY"?
=MATCH(11,Data!$AY:$AY,0)
The information is contained in column AY labeled "New-Rate" in the data sheet
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
I have used index,match for a two item lookup. But what is the formula for three items?? I have attached a file for an example.
View 2 Replies View RelatedI'm looking for click event code for a list box called lbActiveItemList that finds a match for the selected record in Column 1, to the Sheet ReturnData in Column A from row 6 on (using the G column to find the last row). Upon finding a match it should close the userform, and make the G cell for the matching record the Active Cell.
View 7 Replies View RelatedI have managed to set up a dynamic range called "Managers" which is held in a worksheet named "Lists" to validate entries in Col D of a worksheet named "PartTimeStaff" -
I also have 2 userforms which either add or delete managers names from the dynamic range, this all works well.
What I need to do when using the Delete form is to check that the managers name is not selected in any cell of Col D in the "PartTimeStaff" worksheet - this is to ensure the user has reassigned the records to another manager before deleting selected manager on ListBox1within this form.
The code which runs from a command button is below
Private Sub cmbDelete_Click()
Dim i As Integer
Dim SelectionsIndex() As String
Dim ArraySize As Integer
Dim RowToDelete As String
Application.ScreenUpdating = False
With Sheet2
.Visible = True
.Activate
End With
Set ManagerStartRng = Range("A3")
Application.EnableEvents = False
If MsgBox("Are you sure you want to delete this manager?" & vbCr & vbCr & _
"This action cannot be undone!", vbQuestion + vbYesNoCancel, "Confirm Delete") = vbYes Then
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
View 2 Replies View RelatedI have Useform1 & Textbox1 & Textbox2 & CommandButton1
*Textbox1 = Identifier where to put the "X" mark when data is found
*TextBox2 = The User Data 'It is a multiline textbox
*CommandButton1 = Execute the macro
*Excel Column "B3" = Where the textbox2 data will be compared. this one has default data.
*Excel Row 2 = the identifier where to put the "X" mark when the data is found.
The user will enter data in textbox2, For each TextBox2 Value it will be compared in the Data in Column B3 only If the Textbox1 Value found in Row2 which has the codes. Then when the Value is found. it will mark with "X" in the column where the TextBox1 value found. see my screenshot.
Form Screenshot : Capture2.PNG
ExcelSheet Screenshot : Capture1.PNG
Right now I only have this code.
[Code] ....
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies View RelatedI am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
Excel Userform
VB:
'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected)
Private Sub ListBox1_Click()
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
View 4 Replies View RelatedI 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.
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:
When we should use scripting dictionary?
Any website recommendation on the net with a good explanation about this, related to use it in excel?
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
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
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] .....
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.
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
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