VBA宏或条件格式化以识别指定的案件编号。

huangapple
go评论72阅读模式
英文:

VBA Macro or Conditional Formatting to identify the Specified case numbers

问题

Sample Data: VBA宏或条件格式化以识别指定的案件编号。

Expected result:VBA宏或条件格式化以识别指定的案件编号。

Datatable:

Suborder_ID SUBJ_FIRST_NAME SUBJ_LAST_NAME SUPPLR_ID case_number Case Types
296305917 SUSAN LYNCH 772NC 01CR 030779 CR
296305917 SUSAN LYNCH 772NC 03CR 056572 CR
296305917 SUSAN LYNCH 772NC 01D 030779 D Cases
296305917 SUSAN LYNCH 772NC 03CR 056572 D Cases
296305122 JESSICA PURDY 772NC 22CR 719383 D Cases
296305122 JESSICA PURDY 772NC 22CR 734624 D Cases
296305122 JESSICA PURDY 772NC 12CR 219981 D Cases
296306462 JEFFREY HOWARD 772NC 10CR 706096 CR
296306462 JEFFREY HOWARD 772NC 10CRS 706096 CRS
296306462 JEFFREY HOWARD 772NC 09CR 734576 D Cases
296314697 ANTONIO COWARD 772NC 14CR 744035 D Cases
296314697 ANTONIO COWARD 772NC 16CR 730615 D Cases
296314697 ANTONIO COWARD 772NC 17D 768617 D Cases
296314697 ANTONIO COWARD 772NC 17S 768617 S Cases
298183468 KEVIN REMMER 971NC 19CRS 725453 CRS
298183468 KEVIN REMMER 971NC 19S 725453 S Cases
291903468 EBONY EDDIE 34NC 11CRS 030779 CRS
291903468 EBONY EDDIE 78NC 22CR 734629 CR
291903468 EBONY EDDIE 09NC 22CR 764629 CR
291903468 EBONY EDDIE 09NC 11D 706096 CR
英文:

Sample Data: VBA宏或条件格式化以识别指定的案件编号。

Expected result:VBA宏或条件格式化以识别指定的案件编号。

Datatable:

Suborder_ID SUBJ_FIRST_NAME SUBJ_LAST_NAME SUPPLR_ID case_number Case Types
296305917 SUSAN LYNCH 772NC 01CR 030779 CR
296305917 SUSAN LYNCH 772NC 03CR 056572 CR
296305917 SUSAN LYNCH 772NC 01D 030779 D Cases
296305917 SUSAN LYNCH 772NC 03CR 056572 D Cases
296305122 JESSICA PURDY 772NC 22CR 719383 D Cases
296305122 JESSICA PURDY 772NC 22CR 734624 D Cases
296305122 JESSICA PURDY 772NC 12CR 219981 D Cases
296306462 JEFFREY HOWARD 772NC 10CR 706096 CR
296306462 JEFFREY HOWARD 772NC 10CRS 706096 CRS
296306462 JEFFREY HOWARD 772NC 09CR 734576 D Cases
296314697 ANTONIO COWARD 772NC 14CR 744035 D Cases
296314697 ANTONIO COWARD 772NC 16CR 730615 D Cases
296314697 ANTONIO COWARD 772NC 17D 768617 D Cases
296314697 ANTONIO COWARD 772NC 17S 768617 S Cases
298183468 KEVIN REMMER 971NC 19CRS 725453 CRS
298183468 KEVIN REMMER 971NC 19S 725453 S Cases
291903468 EBONY EDDIE 34NC 11CRS 030779 CRS
291903468 EBONY EDDIE 78NC 22CR 734629 CR
291903468 EBONY EDDIE 09NC 22CR 764629 CR
291903468 EBONY EDDIE 09NC 11D 706096 CR

I tried to identify the same Case numbers contain different alphabets like D,CR, CRS, and S, so I applied =RIGHT(E2, 6) to take the last six digits, and if a number greater than 1 is mentioned as exist, else it does not exist, but for some cases the number repeated in other suborder ids is also expected result  is getting collapsed.

can anyone help?

答案1

得分: 1

如果我理解您的要求正确的话，COUNTIFS 是您的好朋友。通过这个公式，可以组合多个条件。
您的第一个条件是子订单ID匹配，第二个条件是案件编号匹配。

假设您在列M中有一个辅助列（使用公式=RIGHT(E2, 6)），列O的公式将是：

=IF(COUNTIFS(A:A,A2,M:M,M2)>1,"存在","不存在")

如果您想在没有辅助列O的情况下在条件格式中使用此公式，只需使用=COUNTIFS(A:A,A2,M:M,M2)>1作为公式。

英文:

If I understand your requirements correctly, COUNTIFS is your friend. With that formula, it is possible to combine multiple criterias.
Your first criteria is that the suborder ID is matching, the second is that the Case Number is matching.

Assuming that you have the helper column in M (with the formula =RIGHT(E2, 6)), the formula for col O would be

=IF(COUNTIFS(A:A,A2,M:M,M2)>1,"Exists","Does Not Exist")

If you want to use this in conditional formatting without helper column O, simply use =COUNTIFS(A:A,A2,M:M,M2)>1 as formula

huangapple
  • 本文由 发表于 2023年6月6日 16:24:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/76412726.html
  • excel
  • excel-2010
  • excel-formula
  • function
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定