#1
|
|||
|
|||
Pairing duplicates in different columns?
I've gotten a large spreadsheet with patient IDs and dates of surgery. One column is patient ID (formatted as YYYYMMDDXXXX), another column is date of surgery (formatted as YYYY-MM-DD). The same patient could have had multiple surgeries, and thus be entered in the sheet multiple times but with different dates of surgery. What I want to do is find true duplicate entries, that is duplicates of both patient ID and date of surgery. How do I do this? Basically all dates of surgery have duplicates since there are no days with jus a single surgery. And many patients have had multiple surgeries (revision surgeries etc), so many patient IDs are duplicate. But I have no idea how to relly pair those up? |
#2
|
|||
|
|||
The simplest way is to add a helper column with formula like =(Date(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))=B2) (replace cell references according your table structure), and then setting autofilter for this helper column to TRUE
|
#3
|
||||
|
||||
There are many possible solutions. Please post a sample sheet (no pics please) where the patient's name/ ID have been anonymized, but without changes to the dates, as they might be text looking like dates, not real dates ( formatting is how dates are presented to the outside world, it's the underlying data that is important.
If, after removing eventual manual alignments, your dates are left aligned in the cell, they are text ( although they look OK) requiring some additional treatment
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting showing duplicates across multiple columns not NOT same row | Peterson | Excel | 2 | 04-05-2021 09:43 PM |
Merging all data between two sheets into a third one without duplicates or rows or columns | Ricko_uk | Excel | 4 | 08-27-2020 01:16 AM |
Split selected columns, or all columns exept for Column1 | Cendrinne | Word VBA | 2 | 09-20-2019 09:14 PM |
Macro to check against Columns & Delete Duplicates | cjamps | Excel Programming | 27 | 12-18-2017 06:38 AM |
Duplicates | riothecat | Excel | 1 | 02-28-2017 03:31 PM |