Excel relationship/replace ?

Hello,

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 FreedmanAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor 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

0
 
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.
0
 
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!
Example.xlsx
0
 
Michael FreedmanAuthor Commented:
Great! Thank you! I have no idea what it means but a friend says he understands. haha
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Michael! Glad to help.
Thanks for the feedback. :)
0
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.

All Courses

From novice to tech pro — start learning today.