Excel:当名称是一列中较长字符串的一部分时，在列之间查找匹配的名称 [英] Excel: finding matching names between columns when names are part of longer string in one column

问题描述

我的一个专栏/列表填充了约会信息(A列)，另一列/临床医生名称列表(C列).我有兴趣将A栏简化为仅临床医生的姓名(与C栏相匹配).是否有一种方法/方法可用于在A和C列中找到匹配项，然后在B列中列出它们?

I have one column/list filled with appointment information (column A), and another separate column/list of clinician names ( column C). I am interested in simplifying column A down to just a clinician name(its match from column C). Is there a method/approach which could be used to find matches in column A and C, and then list them in column B?

A列大约有1100行，C列大约有200行(名称).

Column A has about 1,100 rows, Column C about 200 rows(names).

提前感谢您的时间和考虑！

Thank you in advance for your time and consideration!

A列 预约信息 2018年6月8日上午10:00 (240分钟) AA MH/PHD测试CLC = IP 估计病人 CID/PID:08/06/2018 (等待:0天) 预定于:6/13/2018 创建人:Suzie Chapstick 评论:F107 没出现次数:4 到诊所的距离:不适用

Column A Appointment Info 08/06/2018 @ 10:00 AM (240 min) AA MH/PHD Testing CLC=IP Est Patient CID/PID:08/06/2018 (Wait: 0 days) Scheduled on: 6/13/2018 By: Suzie Chapstick Comments: F107 No Show Count:4 Miles to Clinic: NA

08/08/2018 @ 12:00 PM (120分钟) AA MHC/CHOL-Harpman EVAL 新病人 CID/PID:07/03/2018 (等待:36天) 预定于:7/3/2018 创建人:Legg，Stanley 评论:每MHC NoShow计数:7 到诊所的距离:不适用

08/08/2018 @ 12:00 PM (120 min) AA MHC/CHOL-Harpman EVAL New Patient CID/PID:07/03/2018 (Wait:36 Days) Scheduled on: 7/3/2018 By: Legg, Stanley Comments: Per MHC NoShow Count: 7 Miles to Clinic: NA

08/06/2018 @ 09:00上午 (180分钟) AA MHC/PSY-毒刺摄入量 新病人 客户ID/PID:6/7/2018 (等待:60天) 预定于:7/6/2018 创建人:芬克拜纳(Mink) 评论:每MHC NoShowCount:3 到诊所的距离:16

08/06/2018 @ 09:00 AM (180 min) AA MHC/PSY-Stinger Intake New Patient CID/PID: 6/7/2018 (Wait:60 days) Scheduled on: 7/6/2018 By: Finkbeiner, Maria Comments: Per MHC NoShowCount: 3 Miles to Clinic: 16

C栏 棕色的 邓肯 芬利 哈普曼 毒刺

Column C Brown Duncan Finley Harpman Stinger

推荐答案

您可以尝试以下方法:

=IFERROR(INDEX(Clinicians,MATCH(1,MATCH("*"&Clinicians&"*",A1:A10,0),0)),"not found")

这是我的结果:

我给临床医生提供了一系列帮助提高可读性的方法，A上校也可以这样做.我还建议临床医生应完全放在单独的选项卡上.

I gave the Clinicians a range to help the readablity, the same could be done with Col A. I'd also suggest the clinicians be on a separate tab altogether.

第一个未找到"在临床医生列表中没有命中"，第二个和第三个是相对的空白单元格.

The First "not found" has no "hits" in the clinicians list, the second and third are opposite blank cells.

希望有帮助

祝你好运

这篇关于Excel:当名称是一列中较长字符串的一部分时，在列之间查找匹配的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！