• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Which names listed on List 2 are also in List 1?

P

persol

New Member
I would like to find a way to find the names from LIST 2 in LIST 1 (see lists below), mark them in some color, and pull any additional information in the next column. IS that a difficult formula?

Please note that the names from LIST 1 are mixed with other words or are low caps, yet are still lines that contain the names in question.


"lLIST 2"


Jean-Claude

EDWIN

Onil

Arthur

Edwin

ONIL

ARTHUR

edWIN

Traonil

Doreen


"LIST 1"


SERGIO ARAYAedwin FIGUEROA

WASHINGTON edWIN Onil ALCALDE POBLETE

CARLOS Onil PEREZ QUEZADA

ESTRUCTURAS Onil METALICAS LASAM LTDA.

PROVEEDORA TECNICA INDUSTRIALEdwin Onil LTDA.

METALURGICA CORNEJO ONIL LTDA

METALURGICA CORNEJO ONILLTDA

MECANICA INDUSTRIALArthur MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIAL Arthur MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIALArthur MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIAL ARTHUR MARIA JULIA MATURANA S.A.

Wexmeher & Jean Claude Cia

MarceloTraonil

Doree Smith-Temple Onil

COMERCIALIZADORA J J AGUIRRE LTDA.

MAESTRANZA CHILE LTDA.

PAZ UGARTE ADULTO MAYOR EIRL

ROLEC COMERCIAL E INDUSTRIAL S.A

MAESTRANZA CHILE LTDA.

FUNDICION LAS ROSAS S.A.

MAESTRANZA ALMAHUE LTDA.

ECOMET S.A.

FUNDICION LAS ROSAS S.A.

PAZ UGARTE ADULTO MAYOR EIRL

RAUL PLANELLS Y CIA. LTDA.

GSA S.A

MAESTRANZA CHILE LTDA.

FOSFOQUIM S.A.

ZAMBON Y ZAMBON S.A.

GERDAU AZA S.A.

TERMOINGENIERIA LTDA.

MAIGAS COMERCIAL S.A.

EIFFEL CONSTRUCCIONES METALICAS LTDA.

MAESTRANZA CHILE LTDA.

CGB SERVITRAILER S.A.

MARIO SILVA TAPIA

METALURGICA SILCOSIL LTDA
 
Hi, persol!


If both lists are relatively short (as in this case), you can try this:

a) put List1 in column A from A2 in advance

b) put List2 in row 1 from B1 in advance

c) set formula in B2 to:

=SI(ESERROR(HALLAR(B$1;$A2));"";HALLAR(B$1;$A2)) -----> in english: =IF(ISERROR(SEARCH(B$1,$A2)),"",SEARCH(B$1,$A2))

d) copy B2 down and across as needed


If you get a blank, the string in first row isn't within the text in first column; if you got a number, it's found in that position.


Hope it helps.


Regards!
 
Alright SirJB7, I never suspected that formula, it took me a bit to realize what the numbers meant. Thank you so very much.


However, I am still not there entirely. I shortened the list where I have names mixed in with and without spaces, with and without caps, and mixed with last names. I would like the result to be the name the formula finds rather than the number. I used your formula and adapted a bit more (=IF(ISERROR(SEARCH(B2,A:A)),"",SEARCH(B2,A:A))) but then somehow it is not working anymore. Kindly highlight my error or would you please suggest a better way? The formula should be able to find what is listed in LIST 2 on the LIST1 (it would be at least 2,000 records), the ideal formula is to locate digits (numbers, letters, or names) hidden within the LIST 1. Please help me?


"LIST 1"

SERGIO ARAYA fred FIGUEROA

WASHINGTON Fred Onil ALCALDE POBLETE

CARLOS Onil PEREZ QUEZADA

ESTRUCTURAS Onil METALICAS LASAM LTDA.

PROVEEDORA TECNICA INDUSTRIALMark Onil LTDA.

METALURGICA CORNEJO onil LTDA

METALURGICA CORNEJO OnilLTDA

MECANICA INDUSTRIALMark MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIAL Fred MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIALMARK MARIA JULIA MATURANA S.A.

MECANICA INDUSTRIAL AMARKMARIA JULIA MATURANA S.A.

MECANICA INDUSTRIAL MARK MARIA JULIA MATURANA S.A.

PROVEEDORA darwin INDUSTRIALMark Onil LTDA.

PROVEEDORA TECNICA INDUSTRIALMark Onil LTDA. DARWIN


"LIST 2"

MARK

DARWIN

ONIL

Mark

Darwin

Onil

mark

fred figueroa

onil

Fred Onil

FRED

Carlos Onil
 
Hi, persol!


If you maintain the structure of List1 in column A and List2 in row 1, you should leave the fixed rows and columns with the $ symbol as I stated in my formula; if you don't, only cell B2 will show a correct value.


BTW, the result displayed as a number is the position where the string in row 1 was found within the string in column A, so the text found will always be that of row 1. Is that what you intended to ask?


I didn't keep a copy of the workbook I used for testing, so consider uploading a sample file for further analysis of your updated formulas.


Regards!
 
Hi ,


There is an identical question asked in the LinkedIn Excelhero group !


Check out this link :


http://www.linkedin.com/groups/I-would-like-find-way-3843467.S.113476004?view=&gid=3843467&type=member&item=113476004&trk=eml-anet_dig-b_nd-pst_ttle-cn


Narayan
 
Hi, NARAYANK991!


Are you trying to tell me that this guy persol is cross-posting here and at ExcelHero?


Oh, what an absolutely reprehensible practice!


He should have read the first three green sticky posts at this forums main page... so he'd have avoided what follows.


Regards!
 
@persol


Hi!


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!
 
Hi Pablo ,


Sorry , but I cannot understand what is happening here ! The person who has posted the question in the LinkedIn group appears to be different from the person who has posted the question in this forum !


Narayan
 
Sorry folks, you must have me confused. That other person is probably copying or doing a similar thing. This happened 6 or 7 months ago with a different user. I certainly am aware of the rules and am sure there is a similar but not the same case.


Now, to SirJB7,

thank you very much for your support. I am now testing it fits the bill.


Cheers,

Edwin
 
You must log in or register to reply here.
Back
Top