Any VBA or Formula to find name and past in specified column

[Muhammad Tanzeel Ur Rehma]

Hello everyone,
Wish you will all be happy and healthy and wana thanks in advance to all the champions.

Problem:
i have an excel sheet which i prepared daily, weakly and monthly having column names Date, Reference, Customer/vender, and description. i also have a column name unique names.

what i want?
I want a VBA code that will take the name from unique name list and find the name in description. If name found in description then write this name in Customer/Vender column name otherwise write nothing/leave blank.

again very thanks to all of you!

Regards

Date for seperating customers.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Posed Date	Referenc	Customer/vender	Description	Amount					Unique name
2	1-Jan-22			AC-KHALIFA SONS-BANK DRAFT	2500					tanzeel
3	2-Jan-22			PURCHASE FROM MALIK & SONS	1200					khalifa
4	4-Jan-22			TRANSFER TO FARISHTA	1500					farishta
5	6-Jan-22			JOHN MICK PENALITY PAID	2500					john mick
6	8-Jan-22			SALE OF FUEL TO KASHIF	2000					suleman
7	12-Jan-22			FEE RECEIVED FROM NAZIR AHMED	2500					lesco
8	23-Jun-22			CLEANING FEE PAID TO TANZEEL	1000
9	28-Jun-22			TUTION FEE PAID TO SULEMAN	500
10	31-Jul-22			TRANSFER TO KAMAL	200
11	30-Jul-22			ELECTRICITY PAID TO LESCO	150
12	18-Jul-22			WATER CHARGES PAID TO HELMON	89
13	22-Mar-22			money transfer to tanzeel	12
Sheet1

 

[JoeMo]

Here's some untested code that you can try. Note that it assumes Unique names will always be lower case as in your posted example.
Warning this will assign the customer/vendor name john mick to a description that's like JOHN MICKSON PAID because it contains JOHN MICK.

Sub AddToCustVendList()
'Assumes unique name will always be in lower case
Dim Rdes As Range, Ru As Range, ct As Long, Vdes, Vu, Vout, i As Long, j As Long
Set Rdes = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row): Vdes = Rdes.Value
Set Ru = Range("J2:J" & Cells(Rows.Count, "J").End(xlUp).Row): Vu = Ru.Value
ReDim Vout(1 To UBound(Vdes), 1 To 1)
For i = 1 To UBound(Vdes, 1)
    For j = 1 To UBound(Vu, 1)
        If LCase(Vdes(i, 1)) Like "*" & Vu(j, 1) & "*" Then
            ct = ct + 1
            Vout(i, 1) = Vu(j, 1)
            GoTo Nx
        Else
            Vout(i, 1) = ""
        End If
    Next j
Nx:
Next i
If ct > 0 Then
    Rdes.Offset(0, -1).Value = Vout
    MsgBox "Finished- " & ct & " customer/vendor names added"
Else
    MsgBox "Finished - no customer/vendor names added"
End If
End Sub

 

[Peter_SSs]

Does it need to be vba?

22 08 21.xlsm
	C	D						J
1	Customer/vender	Description						Unique name
2	khalifa	AC-KHALIFA SONS-BANK DRAFT						tanzeel
3		PURCHASE FROM MALIK & SONS						khalifa
4	farishta	TRANSFER TO FARISHTA						farishta
5	john mick	JOHN MICK PENALITY PAID						john mick
6		SALE OF FUEL TO KASHIF						suleman
7		FEE RECEIVED FROM NAZIR AHMED						lesco
8	tanzeel	CLEANING FEE PAID TO TANZEEL
9	suleman	TUTION FEE PAID TO SULEMAN
10		TRANSFER TO KAMAL
11	lesco	ELECTRICITY PAID TO LESCO
12		WATER CHARGES PAID TO HELMON
13	tanzeel	money transfer to tanzeel
Insert name

Cell Formulas
Range	Formula
C2:C13	C2	=IFNA(LOOKUP(9^9,SEARCH(J$2:J$7,D2),J$2:J$7),"")

If it does need to be vba then we can still utilise that formula within the vba.

Sub InsertNames()
  With Range("C2:C" & Range("D" & Rows.Count).End(xlUp).Row)
    .Formula = Replace("=IFNA(LOOKUP(9^9,SEARCH(J$2:J$#,D2),J$2:J$#),"""")", "#", Range("J" & Rows.Count).End(xlUp).Row)
    .Value = .Value
  End With
End Sub

Both of my suggestions come with the same warning that @JoeMo described.

Warning this will assign the customer/vendor name john mick to a description that's like JOHN MICKSON PAID because it contains JOHN MICK.