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