does a cell contain (in any part of it) values from a list - THEN - paste the value in next column.

J

jhbloemarts

New Member
Joined
Nov 28, 2012
Messages
7
I'm looking for a formula to:

Column A - contains list of names. Column a has 1000+ values
A1 : jjjj john jjj
A2 : JOHN djfkdjf
A3 : kdfjkdjf John
A4 : sdslkdl dave
A5 : ddsjkdave
A6 : terry
A7 : kjdkfjd Terry 030

Column C - contains lookup values (column c contains 50 values)
C1 : john
C2 : steve
C3 : dave
C4 : terry

In column E - i want to look in column A and confirm a match to column C.
Answers in column E would be:
E1 : john
E2 : john
E3 : john
E4 : dave
E5 : dave
E6 : terry
E7 : terry


It's basically a VLOOKUP but then not an exact match.
I should not do a best guess because EXCEL really sucks at this.
Maybe the wildcard/mid function.
I found 1 other person looking for the exact same but no trail yet entered with answer.
I've been at it for about 5 nights in a row on multiple multiple forum. It's killing me

Good luck.

Joost Removed E-Mail Address - Moderator
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Code: 
Option Compare Text
Sub a()
LRC = Cells(Rows.Count, "C").End(xlUp).Row
LRA = Cells(Rows.Count, "A").End(xlUp).Row
destcol = 5
destrow = 2
For j = 2 To LRC
  cname = Cells(j, 3).Text
  For i = 2 To LRA
    If InStr(Cells(i, 1), cname) > 0 Then
      Cells(destrow, 5).Value = cname
      destrow = destrow + 1
    End If
  Next
Next
End Sub
 
Upvote 0
Dear Patel,

Your macro nearly works. It however sorts them per name and not per cell found in column A (see results below).

Can you help.

Thanks, Joost.


Option Compare Text
Sub a()
LRC = Cells(Rows.Count, "C").End(xlUp).Row
LRA = Cells(Rows.Count, "A").End(xlUp).Row
destcol = 5
destrow = 2
For j = 2 To LRC
cname = Cells(j, 3).Text
For i = 2 To LRA
If InStr(Cells(i, 1), cname) > 0 Then
Cells(destrow, 5).Value = cname
destrow = destrow + 1
End If
Next
Next
End Sub
[TABLE="width: 872"]
<TBODY>[TR]
[TD]data</SPAN>[/TD]
[TD][/TD]
[TD]CNAME</SPAN>[/TD]
[TD][/TD]
[TD]MACRO from Patel</SPAN>[/TD]
[TD][/TD]
[TD]Answers should be</SPAN>[/TD]
[/TR]
[TR]
[TD]jjjj john jjj</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]steve</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]Marc</SPAN>[/TD]
[/TR]
[TR]
[TD]kdfjkdjf John</SPAN>[/TD]
[TD][/TD]
[TD]dave</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]sdslkdl john</SPAN>[/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]ddsjkdave</SPAN>[/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]dave</SPAN>[/TD]
[TD][/TD]
[TD]dave</SPAN>[/TD]
[/TR]
[TR]
[TD]terry</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 2"]kjdkfjd Terry 030</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[/TR]
[TR]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]kdjfkdjkmarcdkldk</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
Code: 
Sub aaa()
LRC = Cells(Rows.Count, "C").End(xlUp).Row
LRA = Cells(Rows.Count, "A").End(xlUp).Row
destcol = 5
For j = 2 To LRC
  cname = Cells(j, 3).Text
  For i = 2 To LRA
    If InStr(Cells(i, 1), cname) > 0 Then
      Cells(i, 5).Value = cname
    End If
  Next
Next
End Sub
 
Upvote 0
Dear Patel,
Your an absolute wizz?!?!? I'm pretty literate in Q-basic from way back, but i simply can't figure out your math.
Anyway. Works perfectly. Can you also get the CASE SenSitiVe to work as well?

Thanks.
Joost
[TABLE="width: 691"]
<TBODY>[TR]
[TD]data</SPAN>[/TD]
[TD] [/TD]
[TD]CNAME</SPAN>[/TD]
[TD][/TD]
[TD]Patel MACRO</SPAN>[/TD]
[TD]Answer should be…</SPAN>[/TD]
[/TR]
[TR]
[TD]jjjj john jjj</SPAN>[/TD]
[TD] [/TD]
[TD]john</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]marc</SPAN>[/TD]
[TD] [/TD]
[TD]steve</SPAN>[/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]kasjkjaskj John</SPAN>[/TD]
[TD] [/TD]
[TD]dave</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]sdslkdl john</SPAN>[/TD]
[TD] [/TD]
[TD]Terry</SPAN>[/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]ddsjkdave</SPAN>[/TD]
[TD] [/TD]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD]dave</SPAN>[/TD]
[TD]dave</SPAN>[/TD]
[/TR]
[TR]
[TD]TERRy</SPAN>[/TD]
[TD] [/TD]
[TD]joost</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]terry</SPAN>[/TD]
[/TR]
[TR]
[TD="colspan: 2"]kjdkfjd Terry 030</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Terry</SPAN>[/TD]
[TD]terry</SPAN>[/TD]
[/TR]
[TR]
[TD]marc</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]kdjfkdjkmarcdkldk</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]john</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]john</SPAN>[/TD]
[TD]john</SPAN>[/TD]
[/TR]
[TR]
[TD]joost</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]joost</SPAN>[/TD]
[TD]joost</SPAN>[/TD]
[/TR]
[TR]
[TD]jkjskjoost</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]joost</SPAN>[/TD]
[TD]joost</SPAN>[/TD]
[/TR]
[TR]
[TD]ojsjsjsojoostoosjsj</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]joost</SPAN>[/TD]
[TD]joost</SPAN>[/TD]
[/TR]
[TR]
[TD]marc</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]marc</SPAN>[/TD]
[TD]marc</SPAN>[/TD]
[/TR]
[TR]
[TD]jaskjaksj steve</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]steve</SPAN>[/TD]
[TD]steve</SPAN>[/TD]
[/TR]
[TR]
[TD]Steve</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]steve</SPAN>[/TD]
[/TR]
[TR]
[TD]kdfjkdjk Steve</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]steve</SPAN>[/TD]
[/TR]
[TR]
[TD]shjdfhjdhfj STEVE</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]steve</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]
 
Upvote 0
Anyone else that could help me ge this VBA macro to recognise CASE (NOT) SENSITVE lookup funtion from Mr Patel?

Thank you,

Joost.
 
Upvote 0
Code: 
[COLOR=#ff0000]Option compare text[/COLOR]
Sub aaa()
LRC = Cells(Rows.Count, "C").End(xlUp).Row
LRA = Cells(Rows.Count, "A").End(xlUp).Row
destcol = 5
For j = 2 To LRC
  cname = Cells(j, 3).Text
  For i = 2 To LRA
    If InStr(Cells(i, 1), cname) > 0 Then
      Cells(i, 5).Value = cname
    End If
  Next
Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
MS Excel VBA Code to Delete a Row if the Cells Contains a Partial Match for Values Listed in An Array
Replies
6
Views
355
OilEconomist
O
A
  • Question Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
2K
Alex Blakenburg
A
G
  • Solved
Searching a column for any partial matches from another cell text
Replies
4
Views
1K
Joe4
Joe4
L
  • Question Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
873
etaf
etaf
P
  • Solved
validate a character string within a cell to a list and return the value found
2
Replies
10
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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