• 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.

Find Duplicate text within a cell

belindac

New Member
Hi there,

Can anyone please help me with a formula which will identify duplicate text in column I:I when the date in column B is the same??

The text in colum I are initials of people. I want the formula to flag if we have rostered the same initials/person on the same day on a different course.

Thank you in advance.

74888
 
What version of Excel do you have? I made a start but the formula still needs the FastExcel paid add-in and relies upon the use of 365 functions.
Code:
= LET(
  String, TEXTJOIN(",",,FILTER([CTI Initial], [Date]=[@Date])),
  List, TRIM(SPLIT.TEXT(String,",")),
  Attending, TRIM(SPLIT.TEXT([@[CTI Initial]],",")),
  N(SUM(N(Attending=TRANSPOSE(List)))>COUNTA(Attending)) )
74917
 
What version of Excel do you have? I made a start but the formula still needs the FastExcel paid add-in and relies upon the use of 365 functions.
Code:
= LET(
  String, TEXTJOIN(",",,FILTER([CTI Initial], [Date]=[@Date])),
  List, TRIM(SPLIT.TEXT(String,",")),
  Attending, TRIM(SPLIT.TEXT([@[CTI Initial]],",")),
  N(SUM(N(Attending=TRANSPOSE(List)))>COUNTA(Attending)) )
View attachment 74917
Hi Peter, thank you so much for your help with this. I have Excel O365 v16, but I don't have access to paid ad ins on my work computer :(
 
Maybe try at L2

=IF(MAX(COUNTIFS([Date],B2,[CTI Initial],{"","",", *",", *"}&FILTERXML("<x><m>"&SUBSTITUTE(I2,", ","</m><m>")&"</m></x>","//m")&{"","*, ","","*, "}))>1,"duplicate","")
 

Attachments

  • AD HOC REQUESTS TEST.xlsx
    29.4 KB · Views: 5
It all gets a bit bulky without the add-in or a Lambda function to split the strings!
Code:
= LET(
    singleString,[@[CTI Initial]],
    compoundString, TEXTJOIN(",",,FILTER([CTI Initial], [Date]=[@Date])),
    List, LET(
      xml, "<str><item>"&SUBSTITUTE(compoundString, ",","</item><item>")&"</item></str>",
      xpath, "//item",
      IF(ISBLANK(singleString), "", TRIM(FILTERXML(xml, xpath))) ),
    Attending, LET(
      xml, "<str><item>"&SUBSTITUTE(singleString, ",","</item><item>")&"</item></str>",
      xpath, "//item",
      IF(ISBLANK(singleString), "", TRIM(FILTERXML(xml, xpath))) ),
  N(SUM(N(TRANSPOSE(Attending)=List))>COUNTA(Attending)) )
The idea is to list the attendees for a specific course as a row and list the students attending on the day vertically. There should be one match only for each attendee.
 

Attachments

  • AD HOC REQUESTS TEST.xlsx
    40.4 KB · Views: 3
Here is another formula option using Filterxml + Textjoin function

Remark : I add some duplicate in Column I for testing purpose

In L2, array (CSE) formula copied down :

=IF(COUNT(SEARCH(" "&FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(", ",,IF([Date]=B2,[CTI Initial]&"","")),", ","</b><b>")&"</b></a>","//b[preceding::*=.]")&","," "&I2&",")),"duplicate","")

74952
 

Attachments

  • AD HOC REQUESTS TEST (BY).xlsx
    46.8 KB · Views: 4
Last edited:
Maybe try at L2

=IF(MAX(COUNTIFS([Date],B2,[CTI Initial],{"","",", *",", *"}&FILTERXML("<x><m>"&SUBSTITUTE(I2,", ","</m><m>")&"</m></x>","//m")&{"","*, ","","*, "}))>1,"duplicate","")

Hey Excel Wizard, thanks! This worked for the first date, but not the rest :(
 
It all gets a bit bulky without the add-in or a Lambda function to split the strings!
Code:
= LET(
    singleString,[@[CTI Initial]],
    compoundString, TEXTJOIN(",",,FILTER([CTI Initial], [Date]=[@Date])),
    List, LET(
      xml, "<str><item>"&SUBSTITUTE(compoundString, ",","</item><item>")&"</item></str>",
      xpath, "//item",
      IF(ISBLANK(singleString), "", TRIM(FILTERXML(xml, xpath))) ),
    Attending, LET(
      xml, "<str><item>"&SUBSTITUTE(singleString, ",","</item><item>")&"</item></str>",
      xpath, "//item",
      IF(ISBLANK(singleString), "", TRIM(FILTERXML(xml, xpath))) ),
  N(SUM(N(TRANSPOSE(Attending)=List))>COUNTA(Attending)) )
The idea is to list the attendees for a specific course as a row and list the students attending on the day vertically. There should be one match only for each attendee.

Thanks for all your work on this
 
Here is another formula option using Filterxml + Textjoin function

Remark : I add some duplicate in Column I for testing purpose

In L2, array (CSE) formula copied down :

=IF(COUNT(SEARCH(" "&FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(", ",,IF([Date]=B2,[CTI Initial]&"","")),", ","</b><b>")&"</b></a>","//b[preceding::*=.]")&","," "&I2&",")),"duplicate","")

View attachment 74952
Hey, thanks! This works pretty well. Great job. Can it work on the G column as well as the I column? Also, is there a way to disregard anything in brackets? So it just recognises the initials either 2 or 3 digits) and ignores the bracketed info?
 
Last edited by a moderator:
Hey, thanks! This works pretty well. Great job. Can it work on the G column as well as the I column? Also, is there a way to disregard anything in brackets? So it just recognises the initials either 2 or 3 digits) and ignores the bracketed info?
New question new post. This is the forum rule : "One question , one post"

So, open a new post for your new question together with the attachment and the expected result.

Regards
 
Back
Top