CODEWITHSUNDEEP
excel
Amran
Amran

Reputation: 657

Excel - Search for Match Value

May I know how to search for a match value and only display one time only? For example:

A         B          C (will search for match and display one only)                                 
GHI      2          XXY        
XXY      3          
XXY      5

I am able to output the match value but it will also display the duplicate value. For example, XXY will display twice in column C. Here is the code that I did

=INDEX($A$1:$A$10,MATCH($A1,$A$1:$A$10,))

Upvotes: 0

Views: 70

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

If you are using Excel 2016 use CONCAT formula with IF. IF you are using earlier version you need to create UDF. as below

Function ConcatUDF(Rng() As Variant, ByVal delim As String) As String
Dim a, I As Long
For I = 1 To UBound(Rng, 1)
    If Rng(I, 1) <> "" Then
    ConcatUDF = ConcatUDF & _
        IIf(ConcatUDF = "", "", delim) & Rng(I, 1)
    End If
Next
End Function

Here are the examples to use it. enter image description here enter image description here

Upvotes: 2

Related Questions