Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-11-2022, 06:17 AM
kayli10 kayli10 is offline Pairing duplicates in different columns? Windows 11 Pairing duplicates in different columns? Office 2019
Novice
Pairing duplicates in different columns?
 
Join Date: Oct 2022
Posts: 1
kayli10 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 10-11-2022, 08:11 AM
ArviLaanemets ArviLaanemets is offline Pairing duplicates in different columns? Windows 8 Pairing duplicates in different columns? Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
Reply With Quote
  #3  
Old 10-11-2022, 11:50 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Pairing duplicates in different columns? Windows 10 Pairing duplicates in different columns? Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 10-13-2022, 02:16 PM
kilroyscarnival kilroyscarnival is offline Pairing duplicates in different columns? Windows 10 Pairing duplicates in different columns? Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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
File Type: xlsx concatenate_method.xlsx (13.2 KB, 3 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pairing duplicates in different columns? 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
Pairing duplicates in different columns? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft