• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Excel relationship/replace ?


I have an issue in Excel I am hoping someone can help me with! In spreadsheet I, I have two columns of data, call them A and B (about 17,000 rows).  A and B are related in a way that they are two different identifiers for the same thing. In spreadsheet II, I have just a portion of the rows of Column A (about 9,000 rows). I need to replace the data in Spreadsheet II, Column A with the data from Spreadsheet I, Column B, ensuring that the relationship maintains. Does this make sense? Basically, I am switching which product ID codes I am using in an inventory spreadsheet but I have to make sure I replace the existing codes with the exact new ones. Thanks a million!
Michael Freedman
Michael Freedman
  • 3
  • 2
1 Solution
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Can you upload small sample workbooks along with the desired output mocked up manually?
That will help us to know exactly what you are trying to achieve.
Michael FreedmanAuthor Commented:
Yes, please see attached. Sheet 1 and Sheet 2. Items in yellow on Sheet 2 are complete. This process is very easy with only 20 rows, but I have 17,000. Thanks!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this....
Sub ReplaceValues()
Dim sws As Worksheet, dws As Worksheet
Dim x, y, dict
Dim i As Long
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
x = sws.Range("A1").CurrentRegion.Value
y = dws.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
   dict.Item(x(i, 1)) = x(i, 2)
Next i

For i = 1 To UBound(y, 1)
   y(i, 1) = dict.Item(y(i, 1))
Next i
dws.Range("A1").Resize(UBound(y, 1)).Value = y
Set dict = Nothing
End Sub

Open in new window

Michael FreedmanAuthor Commented:
Great! Thank you! I have no idea what it means but a friend says he understands. haha
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Michael! Glad to help.
Thanks for the feedback. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now