View Single Post
 
Old 12-04-2024, 07:20 AM
kvsrinivasamurthy kvsrinivasamurthy is offline Windows XP Office 2007
Novice
 
Join Date: Oct 2017
Posts: 12
kvsrinivasamurthy is on a distinguished road
Default

In N2 copied uptoV4
=GetCoordinates($A2:$J2,$M2,COLUMNS($N2:N2))

Here is the code for UDF.
Code:
Function GetCoordinates(Ip As Range, cri As String, Ind As Integer)

Dim Tc&, Td&, clms&, S$, Y&
Dim M
With CreateObject("Scripting.dictionary")
clms = Ip.Columns.Count
If clms < 2 Or clms Mod 2 <> 0 Then Exit Function
ReDim R(1 To clms)
    For Tc = 1 To clms Step 2
    S = Ip.Cells(1, Tc) & "," & Ip.Cells(1, Tc + 1)
    If S <> "," And S <> "" Then
       If cri = "Unique" Then
            If Not .exists(S) Then
            .Item(S) = "Y"
            End If
       ElseIf cri = "Duplicates" Then
            If .exists(S) Then
            .Item(S) = "Y"
            Else
            .Item(S) = "N"
            End If
       ElseIf cri = "Non Duplicates" Then
            If .exists(S) Then
            .Item(S) = "N"
            Else
            .Item(S) = "Y"
            End If
        End If
    S = ""
    End If
    Next Tc
    
    For Td = 0 To .Count - 1
    If .items()(Td) = "Y" Then
    ReDim Preserve R(1 To Y + 2)
    M = Split(.keys()(Td), ",")
    R(Y + 1) = M(0): R(Y + 2) = M(1): M = ""
    Y = Y + 2
    End If
    Next Td

End With
If Y >= Ind Then
GetCoordinates = R(Ind)
Else
GetCoordinates = ""
End If
End Function
How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
This function can be used like other functions in excel.
Save file as .xlsm
Attached Files
File Type: xlsm SAMPLE (77) exlforumans.xlsm (28.4 KB, 3 views)
Reply With Quote