Search and Replace Text strings based on Column Header

[Pete2020]

I have 2 columns data in

Sheet 1 -Column A is a Emp ID and Column B is a text with identified text Keywords to be replaced.
Sheet 2- Column A has Emp ID which is a primary key to sheet 1 to compare

Once the column A ID is matched in both sheets, then Column B to Column K based on header name(keyword to search in column B), the respective keywords in each cell are to be replaced.

I have around 10k data Looking for a Macro or a VB function to search and replace.

I am very thankful for creating this wonderful platform and helping us

 

[Pete2020]

Please share me the excel. Peter

 

[Pete2020]

This is my sheet2 data

Seach and replacemrexcel.xlsx
	A	B	C	D	E
1	EMP ID	name	designation	company
2	1101	John Peter	Senior Human Resources Recruiter	Nokiasys
3	1102	Jamuna	Assistant Human Resources Manager	Human Resource
4	1103	Shakambari Nayak	HR Recruiter	Roland & Associates
5	1104	Divakar Nayak	Human Resources Executive	IDS Software Pvt Ltd
6	1105	Veena J	Senior HR Generalist	KGD Architecture
7	1106	Kruthi Or Rmaya	HR Executives	Ish Infotech Private Limited
8	1107	Monica	HR Manager	Consulace Business Solutions
9	1108	abhay punjabi	Human Resources Recruiter	Vertisystem
10	1109	Ravi	HR Partner	Udaya Enterprises
11	1110	Shubhra Tripathy	Senior HR Associate	Adodis Technologies Pvt. Ltd.
Sheet2

 

[Pete2020]

Sheet 1 data

Seach and replacemrexcel.xlsx
	A	B
1	EMP ID	Before Macro
2	1101	I am name and currently working as designation and working at Company
3	1102	I am name and currently working as designation and working at Company
4	1103	I am name and currently working as designation and working at Company
5	1104	I am name and currently working as designation and working at Company
6	1105	I am name and currently working as designation and working at Company
7	1106	I am name and currently working as designation and working at Company
8	1107	I am name and currently working as designation and working at Company
9	1108	I am name and currently working as designation and working at Company
10	1109	I am name and currently working as designation and working at Company
11	1110	I am name and currently working as designation and working at Company
12	1111	I am name and currently working as designation and working at Company
13	1112	I am name and currently working as designation and working at Company
Sheet1

 

[Peter_SSs]

My guess is that you have an EMP ID on Sheet1 that does not exist on Sheet2

As per this comment. Sheet1 has EMP ID 1111 and 1112 but Sheet2 does not have these. How can it do replacements for these when they do not exist in Sheet2?

 

[Peter_SSs]

A further point: Your original samples had curly braces {} around the headings in Sheet2 and the place markers in Sheet1 but these appear to have gone. This could lead to some unexpected results.

Example just to demonstrate
Sheet1
I am name but sometimes I am misnamed

Sheet2 replacement for name is John

Without the curly braces, the result would be
I am John but sometimes I am misJohnd

 

[Pete2020]

Peter.

I deleted 1111 and 1112 for the time being from sheet 1

Now sheet 1 and sheet 2 EMP ID is matched. Also keywords changed to {} Curly brackets
Cross checked whether keywords is exactly matched from sheet and sheet 2 - They are fine

Now,I run the macro - but macro showing an error
s = Replace(s, b(1, j), b(r, j), 1, -1, 1)

 

[Peter_SSs]

Now,I run the macro - but macro showing an error
s = Replace(s, b(1, j), b(r, j), 1, -1, 1)

So please answer all the questions that I asked in post #9

 

[Pete2020]

After going through i observed said few IDs not matched with sheet 1 and Sheet 2.

Please add "Not found" in Macro when EMP ID is not matched aganist Sheet 2

I am very Thankful to you, because you are teaching me well.

Good Learning.

Great excel mates on Mr.Excel forum

 

[Peter_SSs]

After going through i observed said few IDs not matched with sheet 1 and Sheet 2.

Please add "Not found" in Macro when EMP ID is not matched aganist Sheet 2

Try

Sub InsertDetails_v02()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, r As Long, ubb2 As Long
  Dim s As String
  
  b = Sheets("Sheet2").Range("A1").CurrentRegion.Value
  ubb2 = UBound(b, 2)
  Set d = CreateObject("Scripting.Dictionary")
  For i = 2 To UBound(b)
    d(b(i, 1)) = i
  Next i
  With Sheets("Sheet1")
    a = .Range("A2", .Range("B" & .Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      r = d(a(i, 1))
      If r = 0 Then
        s = "Not Found"
      Else
        s = a(i, 2)
        For j = 2 To ubb2
          s = Replace(s, b(1, j), b(r, j), 1, -1, 1)
        Next j
      End If
      a(i, 2) = s
    Next i
    .Range("C2").Resize(UBound(a)).Value = Application.Index(a, 0, 2)
  End With
End Sub

 

[Pete2020]

Always the Best from You.

Thank You.