Lookup or match data... not sure how to title this

[Outdoorsman80]

I have a spreadsheet that has combined exported data from multiple sources.

Columns:
[TABLE="width: 603"]
<tbody>[TR]
[TD]MRN
[/TD]
[TD]PATIENT_NAME[/TD]
[TD]MD USER_ID[/TD]
[TD]MD User Name[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Doe, Jane[/TD]
[TD]UI123[/TD]
[TD]Fake, Doc[/TD]
[/TR]
[TR]
[TD]1283[/TD]
[TD]Smith, Jake[/TD]
[TD]UI172[/TD]
[TD]Real, Doc[/TD]
[/TR]
[TR]
[TD]1246[/TD]
[TD]Fake, Name[/TD]
[TD]UI123[/TD]
[TD]Fake, Doc MD[/TD]
[/TR]
[TR]
[TD]2983[/TD]
[TD]Fake, Girl[/TD]
[TD]UI822[/TD]
[TD]Smith, Doc[/TD]
[/TR]
[TR]
[TD]9836[/TD]
[TD]Fake, Guy[/TD]
[TD]UI098[/TD]
[TD]Doe, Doc[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to identify, highlight the discrepancy in the MD User Name column. As you can see, there are 2 listings for Fake, Doc- they are both the same people as indicated by the MD USER_ID but one row has the MD in it while the other doesn't.

How can I do this? I have a massive amount of rows and about 40% of the data has these types of discrepancies for many different doctors.

MRN belongs to the patient name.

 

[jtakw]

Hi,

Not clear on How you want them identified, here are 2 ways, use Column E formula to indicate duplicated entries, use Column G formula as your CF formula to highlight the cell:

Book1
	A	B	C	D	E	F	G
1	MRN	PATIENT_NAME	MD USER_ID	MD User Name	Alert		CF formula for Column D
2	1234	Doe, Jane	UI123	Fake, Doc	Duplicated		TRUE
3	1283	Smith, Jake	UI172	Real, Doc			FALSE
4	1246	Fake, Name	UI123	Fake, Doc MD	Duplicated		TRUE
5	2983	Fake, Girl	UI822	Smith, Doc			FALSE
6	9836	Fake, Guy	UI098	Doe, Doc			FALSE
Sheet13

Cell Formulas
Range	Formula
E2	=IF(COUNTIF(C$2:C$6,C2)>1,"Duplicated","")
G2	=COUNTIF(C$2:C$6,C2)>1

 

[lrobbo314]

If all you want to do is look at the differences, I would create a pivot table. Add the MD User_ID to rows, and then add the MD User_Name to Rows.

Having said that the code below can do 2 things. It can highlight the differences. That is to say, the "MD" part of "Fake, Doc MD" would be highlighted as red text. The code finds the User_Name with the fewest number of characters and highlights the difference in all of the cells with longer lengths. Not sure if the shortest length User_Name is the one you would necessarily want, but that's what it does.

But, I figured that you would rather just change the User_Name to be equal to the shortest User_Name. So, if you run the code the way it is, it will do this.

It will look at each User_ID. Find the shortest User_Name associated to that ID. Then, change all other User_Names associated to that ID to the shortest User_Name. So, for UI123, all MD_UserNames would be 'Fake, Doc'.

This code assumes that your data is in columns A:D, starting in A1. If your data is somewhere else, you will need to adjust the code.

You will also need to add a reference to 'Microsoft Scripting Runtime' for the code to work.

Sub MedII()
Dim AR()
Dim R As Range
Dim LR As Long
Dim Dict As New Dictionary
Dim Current As String
Dim i As Integer
Dim Lowest As Integer


LR = Range("A" & Rows.Count).End(xlUp).Row()
Set R = Range("C2:D" & LR)
AR = R.Value


For i = 1 To UBound(AR)
    Current = AR(i, 1)
    If Not Dict.Exists(Current) Then
        Dict.Add Current, Current
        Lowest = FindLowest(AR, i)
        HighlightDif R.Columns(1), CStr(AR(Lowest, 1)), CStr(AR(Lowest, 2))
    End If
Next i


End Sub


Function FindLowest(ByRef V As Variant, Pos As Integer) As Integer
Dim Low As Integer


Low = Pos


For i = Pos + 1 To UBound(V)
    If V(i, 1) = V(Pos, 1) And Len(V(i, 2)) < Len(V(Pos, 2)) Then
        Low = i
    End If
Next i


FindLowest = Low
End Function


Sub HighlightDif(R As Range, ID As String, uName As String)
Dim cel As Range
Dim Off As Range


For Each cel In Range(R.Address)
    If cel.Value = ID Then
    Set Off = cel.Offset(, 1)
        If Off.Value <> uName Then
'            For i = Len(uName) + 1 To Len(Off.Value) 'This commented block of code will change the font color of the differences to Red
'                Off.Characters(i, 1).Font.Color = vbRed
'            Next i
            Off.Value = uName
        End If
    End If
Next cel
End Sub

 

[Outdoorsman80]

Hi,

Not clear on How you want them identified, here are 2 ways, use Column E formula to indicate duplicated entries, use Column G formula as your CF formula to highlight the cell:

	A	B	C	D	E	F	G
MRN	PATIENT_NAME	MD USER_ID	MD User Name	Alert	CF formula for Column D
Doe, Jane	UI123	Duplicated
Smith, Jake	UI172	Real, Doc
Fake, Name	UI123	Duplicated
Fake, Girl	UI822	Smith, Doc
Fake, Guy	UI098	Doe, Doc

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1234[/TD]

[TD="bgcolor: #FFFF00"]Fake, Doc[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1283[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1246[/TD]

[TD="bgcolor: #FFFF00"]Fake, Doc MD[/TD]

[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2983[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]9836[/TD]

[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=IF(COUNTIF(C$2:C$6,C2)>1,"Duplicated","")[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=COUNTIF(C$2:C$6,C2)>1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi, can you help me understand the formula a bit? If I have say 8000 rows, would I just change C$6 to C$8000 ?


lrobbo314 - I will try that in a bit

 

[jtakw]

Hi, can you help me understand the formula a bit? If I have say 8000 rows, would I just change C$6 to C$8000 ?lrobbo314 - I will try that in a bit

Yes, just change the cell range to match your data, you can even go over a bit in case the table may grow in size.