Search multiple input from one workbook and replace value in another workbook

rsubra

New Member
Joined
Sep 17, 2018
Messages
1
Below is the data in my two different sheets:

Workbook1

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]LastName[/TD]
[TD]FirstName[/TD]
[TD]NewPermission[/TD]
[/TR]
[TR]
[TD][TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl64, width: 177"]Aadhi[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 152"]
<tbody>[TR]
[TD="class: xl66, width: 152"]Vetrivel[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl66, width: 240"]Risk Quality Reviewer II[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl66, width: 177"]Abudhahir[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 152"]
<tbody>[TR]
[TD="class: xl66, width: 152"]Imrana Parveen[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl66, width: 240"]Risk Reviewer II[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 177"]
<tbody>[TR]
[TD="class: xl66, width: 177"]Achari[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 152"]
<tbody>[TR]
[TD="class: xl66, width: 152"]Deepthi[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl66, width: 240"]Risk Quality Reviewer II[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]








Workbook2
[TABLE="width: 500"]
<tbody>[TR]
[TD]LastName
[/TD]
[TD]FirstName[/TD]
[TD]CurrentPermission[/TD]
[/TR]
[TR]
[TD]Aadhi[/TD]
[TD]Vetrivel[/TD]
[TD]Read Only[/TD]
[/TR]
[TR]
[TD]Abudhahir[/TD]
[TD]Imrana Parveen[/TD]
[TD]Read Only
[/TD]
[/TR]
[TR]
[TD]Achari[/TD]
[TD]Deepthi[/TD]
[TD]Read Only[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want to achieve:

I want to be able to search Workbook2 based on LastName and FirstName column coming from Workbook1 and get the NewPermission value and then replace the value of CurrentPermission column in Workbook2 with that of the NewPermission value.

Please advice an easy and efficient way to achieve this as I have to do this for over 1000 rows on regular basis.

Thank you so much for all of your help in advance.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
This would be easier if you could temporarily add a helper column to Workbook2 which is a combination of last & first name,
Change all the red text to your actual references which you could SET ahead of time.

Code:
sub doreplacer
dim lastrow1 as long
dim lastrow2 as long
lastrow1 = [COLOR=#ff0000]workbook1[/COLOR].sheets("[COLOR=#ff0000]whateveryoursheetname[/COLOR]").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastrow2 = [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
rng = [COLOR=#ff0000]combocol_on_workbook2 &"1:combocol_on_workbook2"[/COLOR] & lastrow2
for x = 1 to lastrow1
if iserror(application.match([COLOR=#ff0000]wbk1last&firstname[/COLOR], [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").range(rng),0))=false then
hit = 0 'reset
hit = application.match([COLOR=#ff0000]wbk1last&firstname[/COLOR], [COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").range(rng),0))
if hit>0 then
[COLOR=#FF0000]workbook2[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").cells(hit,[COLOR=#ff0000]CurrentPermissionColumn[/COLOR]) = [COLOR=#FF0000]workbook1[/COLOR].sheets("[COLOR=#FF0000]whateveryoursheetname[/COLOR]").Cells(x,[COLOR=#ff0000]NewPermissionColumn[/COLOR])
endif
endif
next x
msgbox "Complete"
end sub
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top