As Pecoflyer says, it may depend on how your data is formatted.

I tried an experiment - this may not be the fanciest way of achieving this, but it should work. I input some made-up data representing dates and patient info as you indicated.

In both cases, the patient ID part is a number stored as text. I haven't tried it as an integer. I'm presuming that item is entered as text.

I tried the date both ways - entered as a date and the format set to match your YYYY-MM-DD, and also the date typed in as text in the lower portion. You can see in the concatenate, it converts that date to the five digit DATEVALUE.

As long as all the data going down each column is formatted the same (all as dates or all as text) a concatenate of the two columns will provide an exact match when the date and the patient ID are both duplicates in the same row. I applied conditional formatting for duplicates in that columns, and as such, at least in my version of Excel (365), I can apply a filter on that column by color, which would yield just the two duplicated rows.

Does that make sense? I'm sure there's a more elegant way, but it should be simple for you to try on your data.

Hope that helps,

Ann

Attached Files

concatenate_method.xlsx (13.2 KB, 5 views)