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

[Outdoorsman80]

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

Columns:

MRN	PATIENT_NAME	MD USER_ID	MD User Name
1234	Doe, Jane	UI123	Fake, Doc
1283	Smith, Jake	UI172	Real, Doc
1246	Fake, Name	UI123	Fake, Doc MD
2983	Fake, Girl	UI822	Smith, Doc
9836	Fake, Guy	UI098	Doe, Doc

<tbody>
</tbody>

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

<tbody>
</tbody>

Sheet13​

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

<tbody>
</tbody>

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.